Thanks:  0
Likes:  0

1. ## Adding digits of a number

Hello:

I am looking for a formula or function that will add together the separate digits from a number. Example \$156,546.00 would add in another cell as 1+5+6+5+4+6+0+0 the result being 27. Does such a function or formula exist.

Thank you,

Orlando

2. ## Re: Adding digits of a number

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 C1 =

A
B
C
D
E
F
1
\$156,546.00 27
2
3
4
5
 Sheet3

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

3. ## Re: Adding digits of a number

One way...

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

4. ## Re: Adding digits of a number

You could also create a User Defined Function in VBA:

Code:
```Function AddDigits(NumberString As String) As Integer

For i = 1 To Len(NumberString)
CurDigit = Mid(NumberString, i, 1)
Next i

End Function```
Paste this code in the VBE and then use the function =AddDigits(A1).

Hope this helps!

5. ## Re: Adding digits of a number

Aladin and Mark have already suggested solutions that work with numbers. However, if the \$ is part of the cell content (i.e., not part of the formatting), consider using:

=SUM(IF(ISNUMBER(0+MID(H11,ROW(INDIRECT("a1:a"&LEN(H11))),1)),0+MID(H11,ROW(INDIRECT("a1:a"&LEN(H11))),1),0))

where H11 contains the text of interest.

6. ## Re: Adding digits of a number

Originally Posted by tusharm
...However, if the \$ is part of the cell content (i.e., not part of the formatting)...
Due to coercion...

{=SUM(MID(A1*1000,ROW(INDIRECT("1:"&LEN(A1*1000))),1)+0)}

...works for...

{=SUM(MID("\$15,6546.00"*1000,ROW(INDIRECT("1:"&LEN("\$15,6546.00"*1000))),1)+0)}

...too.

7. ## Re: Adding digits of a number

I think Marks would be okay due to the multiplilication (albeit for a different reason - I'm assuming to capture digits after the decimal??). And Aladin's would work as well by modifying H11 to H11+0. Your's would work in a case the case of say 123,456USD where Mark's and Aladin's would fail, though.

8. ## Re: Adding digits of a number

Thank you very much guys.

Orlando

9. ## Re: Adding digits of a number

Better late than never, so here's an improvement, if you will, on Mark W.'s array formula in that it avoids the use of the INDIRECT function:

=SUM(MID(A1*1000,ROW(A1:INDEX(A:A,LEN(A1*1000))),1)+0)

It's also similar to one of J. Walkenbach's formulas ("Microsoft Excel 2000 Bible", pg. 470), although he uses the OFFSET rather than the INDEX function, and the VALUE function rather than numeric coercion. Because of this, his is a longer construction.

Finally, this formula will work if "\$" is part of the cell content, and not just the result of formatting.

--Tom

10. ## Re: Adding digits of a number

Originally Posted by rrdonutz
Better late than never, so here's an improvement, if you will, on Mark W.'s array formula in that it avoids the use of the INDIRECT function:

=SUM(MID(A1*1000,ROW(A1:INDEX(A:A,LEN(A1*1000))),1)+0)

It's also similar to one of J. Walkenbach's formulas ("Microsoft Excel 2000 Bible", pg. 470), although he uses the OFFSET rather than the INDEX function, and the VALUE function rather than numeric coercion. Because of this, his is a longer construction.

Finally, this formula will work if "\$" is part of the cell content, and not just the result of formatting.

--Tom
Tom,

That construct for replacing INDIRECT falls apart when a row is inserted before the formula cell. You need...

{=SUM(MID(A2*1000,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2*1000))),1)+0)}

This is similar to the throuble one runs into when using ROW(1:1) as a counter/indexer.

By the way, SUBSTITUTE(A1,".","") is better than using a fixed multiplier like 1000.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•