sum

[Rahul Mishra]

Dear Sir,

How can i sum 123456789 in single cell and result output is 45

 

[teylyn]

Hello,

with the number in cell C1 you can use

=SUMPRODUCT(MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)+0)

as seen here.

cheers, teylyn

 

[Rahul Mishra]

thanks
can i do this from any other method?

 

[Khalid NGO]

Hi Rahul,
I think this will work:

=SUM(IFERROR(--MID(A2,--ROW(1:100),1),0))
Array formula, to be entered with Ctrl+Shift+Enter

Regards,

 

[Deepak]

Hi Rahul,
I think this will work:

=SUM(IFERROR(--MID(A2,--ROW(1:100),1),0))
Array formula, to be entered with Ctrl+Shift+Enter

Regards,

A little correction...

=SUM(IFERROR(--MID(A2,ROW($1:$100),1),0))

 

[Rahul Mishra]

thank u so much sir

but what difference between Row(1:100)
and Row(C2)

 

[Deepak]

thank u so much sir

but what difference between Row(1:100)
and Row(C2)

Row(1:100) will create a array of rows

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100}

while Row(C2) will be only 2

 

[Khalid NGO]

A little correction...

=SUM(IFERROR(--MID(A2,ROW($1:$100),1),0))

Thank you Sir.

 

[teylyn]

thanks
can i do this from any other method?

What don't you like about it? You don't even have to array enter it.

 

[Rahul Mishra]

no it's good but if i add in this some alphabet error accorded.

 

[Rahul Mishra]

how can i find only numeric data
11331gjgfyufyufyuyufyuguiuiuiui313323jkgjkgh
in this

 

[Deepak]

how can i find only numeric data
11331gjgfyufyufyuyufyuguiuiuiui313323jkgjkgh
in this

@ Rahul Mishra,

I failed to understand why u have asked the same question while we have given the suggestion on then same a month ago where we also didn't find ur reply there!!

http://chandoo.org/forum/threads/find-text-and-numeric.24359