Adding up the digits making up a number in a cell

[Geir]

Hello,

Is there a function in Excel (or an add-on) that would add up (sum) the digits making up a number in a cell. For example, for a cell with the number value 414 it would return in a different cell 9, or for 5411 it would return in a different cell 11 etc..

Is there such a utility/function?!

Thanks for help

[DonkeyOte]

Yes, if A1 holds string eg 5411 then to get total of digits:

B1: =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")),{1,2,3,4,5,6,7,8,9})

[Geir]

I am getting an error in excel using that line... Can you check it?! I am using excel 2003

[DonkeyOte]

The formula is fine, however, I suspect you need to translate the formula per your own regional settings
(the formula was setup in UK region - ie comma rather than semi-colon)

With Swedish settings, perhaps: =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1;{1\2\3\4\5\6\7\8\9};""));{1\2\3\4\5\6\7\8\9})

(not sure if you need to translate the name of the actual functions - ie SUMPRODUCT becomes... I saw someone post a link here not long ago I will see if I can find it)

[NBVC]

..
(not sure if you need to translate the name of the actual functions - ie SUMPRODUCT becomes... I saw someone post a link here not long ago I will see if I can find it)

Here's one link:

Function Translations

[DonkeyOte]

Geir, martindwilson provided a nice link re: translations of functions (Edit: and NBVC above):

http://www.piuha.fi/excel-function-n...languages.html

So based on that I believe

SUMPRODUCT: PRODUKTSUMMA
LEN: LÄNGD
SUBSTITUTE: BYT.UT

[martindwilson]

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

[DonkeyOte]

True, but Volatile.

[martindwilson]

volatile but easier on the typing lol