Add Value of Digits in a Number with a Decimal Point

[Weco Controller]

On a city tax form I need to add the value of the digits in the payment amount. For example if the tax payment is $445.95 I need to add 4+4+5+9+5 which is 27. Is there a formula to add these numbers?

 

[Joe4]

Not sure if you realize this, but you posted this question to the "Test Here" Forum, which is just a place to test posting skills. If this is a real question that you would like answered, please post it to the "Excel Questions" Forum (Excel Questions).

 

[MrMeinHerr]

On a city tax form I need to add the value of the digits in the payment amount. For example if the tax payment is $445.95 I need to add 4+4+5+9+5 which is 27. Is there a formula to add these numbers?

Although this is posted in the wrong forum, I thought I'd help, so that the user may get the help desired...

I'd suggest using an array formula like the following to get the required result.

If the value is in cell A18, this ought to work in Excell 2007 or later:

=SUM(IFERROR(VALUE(MID(A18,ROW(INDIRECT("1:"&LEN(A18))),1)),0))

Be sure to enter the formula with Ctrl+Shift+Enter (and not just Enter) to make it an array formula.

I hope this helps, and you see it before it's deleted!

 

[MrMeinHerr]

By the way, the previous solution assumes you are entering currency amounts. If you happen to enter other formats, like dates, or things like "1/3" or "pi()" for example, and your cells is formatted as currency, you might see unexpected results. To force it to see the value as currency, you might try:

=SUM(IFERROR(VALUE(MID(TEXT(A18,"$0.00"),ROW(INDIRECT("1:"&LEN(TEXT(A18,"$0.00")))),1)),0))

Again, be sure to enter the formula with Ctrl+Shift+Enter (and not just Enter) to make it an array formula.

 

[Peter_SSs]

Although this is posted in the wrong forum, I thought I'd help, so that the user may get the help desired...

The user will get help if they post in the correct forum and that help won't be deleted after a few days as threads in the Test Here forum are.

 

[Joe4]

I have moved the thread to the Excel Questions forum.
In the future, please do not post/respond to threads in the Test Here forum, for the reasons Peter outlines.

 

[Peter_SSs]

For a non-array-entered, non-volatile alternative, try

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

 

[MrMeinHerr]

In the future, please do not post/respond to threads in the Test Here forum, for the reasons Peter outlines.

It won't happen again. Thanks.