Sum of a number's digits

[Guest]

I need to insert check figures on my spreadsheet that equal the sum of a
number's digits. If cell A1 = 12,345.67, is there a formula that in cell A2
would add the digits 1+2+3+4+5+6+7 to equal 28?

Thanks for your help.
Claudia

 

[vezerid]

Claudia,
the following *array* formula (needs to be committed with
Shift+Ctrl+Enter) will calculate what you ask:

=SUM(IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

HTH
Kostis Vezerides

 

[MartinW]

Hi Claudia,

I got this formula from these groups a while ago.
I can't remember who posted it but it has a Bob Phillips
look to it.

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

It will handle the sum of digits in A1 but it doesn't allow for the comma
separator and decimal point in your example. With a bit of data
manipulation you should be able to make it work.

HTH
Martin

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[MartinW]

Yep!

Thought it was you Bob.
And it now has another bit of concise Phillips genius added to it.

Regards
Martin

 

[Guest]

It works great. Thank you!

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[vezerid]

Bob,
can always count on you for a new idea...

Regards,
Kostis

 

[Bob Phillips]

Hi Kostis,

Didn't notice you in there.

Got the details, digesting it now thanks. Unfortunately time is marching on.

Regards

Bob

Bob,
can always count on you for a new idea...

Regards,
Kostis