Sum of a number's digits
 

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

Sum of a number's digits
 

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:"&LE N(A1))),1))),VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1))))

HTH
Kostis Vezerides

Sum of a number's digits
 

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

Sum of a number's digits
 

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

--
HTH

Bob Phillips

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

"Claudia" wrote in message
...
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

Sum of a number's digits
 

Yep!

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

Regards
Martin

Sum of a number's digits
 

It works great. Thank you!

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

"Claudia" wrote in message
...
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

Sum of a number's digits
 

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

Regards,
Kostis

Bob Phillips wrote:
=SUMPRODUCT(--(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("1:"&LEN(S UBSTITUTE(A1
,".","")))),1)))

--
HTH

Bob Phillips

Sum of a number's digits
 

Hi Kostis,

Didn't notice you in there.

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

Regards

Bob

"vezerid" wrote in message
ups.com...
Bob,
can always count on you for a new idea...

Regards,
Kostis

Bob Phillips wrote:

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

--
HTH

Bob Phillips