Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Adding digits of a number

This is a discussion on Adding digits of a number within the Excel Questions forums, part of the Question Forums category; Hello: I am looking for a formula or function that will add together the separate digits from a number. Example ...

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    16

    Default 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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default 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
    =

    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. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,527

    Default Re: Adding digits of a number

    One way...

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

  4. #4
    Board Regular LTunnicliffe's Avatar
    Join Date
    May 2003
    Location
    Round Rock, TX
    Posts
    767

    Default Re: Adding digits of a number

    You could also create a User Defined Function in VBA:

    Code:
    Function AddDigits(NumberString As String) As Integer
      
      AddDigits = 0
      For i = 1 To Len(NumberString)
        CurDigit = Mid(NumberString, i, 1)
        If IsNumeric(CurDigit) Then AddDigits = AddDigits + CurDigit
      Next i
    
    End Function
    Paste this code in the VBE and then use the function =AddDigits(A1).

    Hope this helps!

  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,883

    Default 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. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Adding digits of a number

    Quote 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. #7
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default 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. #8
    New Member
    Join Date
    Apr 2002
    Posts
    16

    Default Re: Adding digits of a number

    Thank you very much guys.

    Orlando

  9. #9
    Board Regular
    Join Date
    Jan 2003
    Location
    Round Rock, Texas
    Posts
    564

    Default 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. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,527

    Default Re: Adding digits of a number

    Quote 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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com