Sum the digits of a single cell down to one number

[Gavald229]

Hello all!

I saw that this function exists for adding all the digits in a cell (for example, A1) into a single number in B1.

Please Login or Register  to view this content.

For example, if I typed the above code into B1, and if A1 was 33, it would take 3+3=6, giving me 6 as the number that shows up in B1. Another example would be for 93, it would take 9+3=12, then 1+2=3, giving me 3 as my end result in B1.

The problem I run into is when the number in A1 is 0. The same code is put into B1, but B1 shows up as 9, not 0. I tried to fiddle around with making an exception to the formula, but I can't seem to figure it out. Is there a way to keep that formula in there, but have 0 show up if the number in A1 is 0? Thanks in advance for your help!

[icestationzbra]

will work properly if you will never have -ve numbers in A1:

Please Login or Register  to view this content.

[Gavald229]

Exactly what I was looking for! Thanks a bunch!

[NBVC]

Does that formula work for any number in A1? I don't think so...

Try instead:

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

[icestationzbra]

@NBVC,

OP wanted that the summation result of any number in A1 should result in a single digit output.

e.g., if A1 = 187, then B1 = 7

the above formula may not help in that case.

[Gavald229]

The first formula that icestationzbra posted indeed works exactly how I wanted it to. The example just given of using the code to have A1 = 187, B1 = 7 is completely correct for what wanted. And yes, it does work with the 0's too.

[ChemistB]

IceStation's formula sums down to a single digit whereas NBVC's sums 1.
For example 987 Sums to 24 (answer to NBVC's formula) and those digits sum to 6 (answer to Icestations' formula)

[icestationzbra]

please do not say that this is my formula; it is OP's formula, i just tweaked it.

OP: by the way, how do you want to deal with -ve numbers?

Please Login or Register  to view this content.

would return -6 when there is -12345 in A1. if you do not want the -ve sign, just drop the "sign(a1)*" from the formula above.

[Gavald229]

@ NBVC & ChemistB - Sorry about that, I should have made myself clearer in the original post. I did want only one digit to return (between 0-9, so 73=11 then 1+1=2), not just the sum of the numbers as a whole (more than one digit, so 73=11) therefore, the formula that icestationzbra posted was more what I was looking for. Thank you for the help, though!

@ icestationzbra I'm not planning (at the moment) to deal with negative numbers, but I'll be sure to store that away somewhere for if / when I need to use them! Thanks again!

[NBVC]

I had simply misunderstood/misread the request... sorry for adding confusion...