how to sum digits within a cell..??

[gov_007]

Hi,


Am compiling some report received from different vendors wherein am facing little issue while adding some digits..as the vendor are sending some data in one cells for a individual person e.g.

[pre]

A      B  c
Ram   24
Vicky 579

[/pre]
where i want the added total of B in C like 24 = 6, 579 = 21 as on ...if anyone can help me using some formula so that i don't have to add it manually...


Thanks & Regards,

Abhinav

 

[Luke M]

This formula should do the trick:

=SUMPRODUCT(1*(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)))

 

[gov_007]

Great ...thanks...

 

[Luke M]

You're welcome. =)

 

[gov_007]

Hi Luke,


This formula is awesome..but request you to help me out in understanding the concept of the above formula..


I mean am unable to understand the concept of using {ROW(INDIRECT("1:"&LEN(B3))),} in the above formula....


Regards,

Abhinav

 

[Haseeb A]

Abhinav,


Consider B3 = 24, B4 = 579


LEN(B3) : will give how many characters in B3, = 2

INDIRECT("1:"&LEN(B3)) : This will make it INDIRECT("1:2"). 1:1, 1:2 are row in excel, same like A1:A1, A1:A2. INDIRECT will convert the string to it's values.


ROW(INDIRECT("1:"&LEN(B3))) : For simplicity you could also use ROW(INDIRECT("A1:A"&LEN(B3))) This will convert ROW(1:2), which is the array of {1;2} | Other one will be like ROW(INDIRECT("A1:A2")) |


MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1) : Will extract each character. MID(B3,{1;2},1) which is MID(B3,1,1), MID(B3,2,1) & WILL GIVE AN ARRAY


1*MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1) : will convert to actual numbers then SUMPRODUCT will SUM them.


Note: If you have any text in the cell even a space this will give error. below is an alternate to work with if you have numbers or texts


=SUM((LEN(B3)-LEN(SUBSTITUTE(B3,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})

 

[Hui]

Refer: http://chandoo.org/wp/2011/03/18/calculating-sum-of-digits-in-a-number/