27 thoughts on “Summing the Digits of a Number”

1. Here’s another one (array entered):

   =SUM(IFERROR(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1,0))

   It ignores everything except the numeric characters in the cell. It uses IFERROR, so it works only with Excel 2007 or later.

   I’ve always thought of this as a solution in search of a problem. What do you use it for. Michael?

2. THis array formula works pre-2007 as well for mixed text and number strings

   =SUM(IF(ISNUMBER(–MID(A1,ROW($1:$256),1)),–MID(A1,ROW($1:$256),1),0))

3. I’d be tempted to use Longre’s MOREFUNC add-in, as in

   =EVAL(REGEX.SUBSTITUTE(REGEX.SUBSTITUTE(x,”D”,””),”(.)”,”+[1]”))

   To sum only the decimal numerals in an arbitrary string, use

   =SUMPRODUCT(LOOKUP(MID(E1,s,1),d&””,N(d)))

   where s is defined as =ROW(INDEX($1:$6656,1,1):INDEX($1:$6656,255,1)) and d is defined as ={“”,1,2,3,4,5,6,7,8,9,”A”}.

4. or you could use a VBA function. i don’t understand the obsession with pure formula.

5. I agree with Rob, a UDF will work better and probably faster:

   Public Function SumDigits(Number As Variant) As Variant
   Dim aByte() As Byte
   Dim j As Long

   aByte = CStr(Number)
   For j = LBound(aByte) To UBound(aByte) – 1 Step 2
   SumDigits = SumDigits + Val(Chr(aByte(j)))
   Next j
   End Function

6. Well, I just timed it on XP SP3, Excel 2003 SP3, and sure your UDF is faster than my formula, and probably faster than Walk’s, but ours caters for non-numeric characters. If the source is all digits as I read Micheal’s post then the last formula that he gives is faster than the UDF by some 7%.

7. Declaring the SumDigits function as a Double and using Chr$ instead of Chr makes it about 10% faster.

8. Hi John –

   Well, the thought was to create a Checksum number for data being created as an input file to a server. What it does in reality is say that one of these two numbers is wrong. And since that hasn’t happened yet, it is still a solution seeking a problem ;-) We’ve got belts and suspenders. How often does an output file get written wrong or an input file read wrong these days?

   Rob – amplifying Dick K’s MVP remarks about new users, I provide corporate XL support, and I find there are users who steer clear of UDF’s. That’s a plateau in their learning still in their future. They have to change their security setting, learn about modules, etc. But give them a formula, and they’re all set.

   …mrt

9. “But give them a formula, and they’re all set.”
   Until they need to do something slightly different, and your services are again required.

   To me, this is nothing more than “Look Ma, No Macros”.
   I think it’s clever, but unsuitable for business.

10. […] Adding Every Other Cell (also at the Microsoft Office Blog: Adding Every Other Cell in a Column ) and Summing the Digits of a Number […]

11. Rob sez: “i don’t understand the obsession with pure formula.”

    If you use a formula, you can be assured that it will actually work. As you know, Microsoft wants people to fear VBA macros and does everything possible to make the user disable macros when a file is opened. And, if you’re doing thousands of such calculations, it’s likely that there will be a noticeable performance hit if you use VBA functions rather than formulas.

12. John – you say “you can be assured that it will actually work”. You mean “work” as in give the correct answer?

    Which version of Excel are you using, and where can I get a copy? :)

    One of the reasons I prefer a UDF to an array with convoluted logic is because it is easier to see how they work, and thus easier to see what’s going wrong, when something goes wrong.

    FWIW, I’ve posted my effort at this problem and the Sum-skip problem at: http://newtonexcelbach.wordpress.com/2009/10/05/three-udfs/

13. Doug – and you say “You mean “work” as in give the correct answer?”

    No, he means it doesn’t work because the user has a low security setting or doesn’t understand what enabling macros means so doesn’t, or doesn’t work because the IT department don’t allow macros/scripts.

14. Bob – OK, that makes sense. Fair point then.

15. The UDF also handles non-numeric characters.
    The version below is about 1/3 faster and is faster than the numerics-only SUMPRODUCT formula, but both are probably fast enough for most real-life purposes.

    I use the Byte array technique for verifying Check Characters, since many check character algorithms are based on non-numeric characters, and its fast enough to handle large ranges.

    Putting UDFs into an XLA or Automation adding packaged with an Installer generally gets around the end-user’s lack of understanding about Macro warnings (BTW when was the last time anyone saw a Macro virus?), but does not solve the problem with paranoid IT departments.

    Public Function SumDigits2(Number As Variant) As Double
        Dim aByte() As Byte
        Dim j As Long
        On Error GoTo Fail
        aByte = CStr(Number.Value2)
        For j = LBound(aByte) To UBound(aByte) – 1 Step 2
            If aByte(j)  47 And aByte(j)  58 Then
                SumDigits2 = SumDigits2 + aByte(j) – 48
            End If
        Next j
        Exit Function
    Fail:
        SumDigits2 = -1
    End Function

16. Hmm.. tag problem with less than and greater than

    If aByte(j)  58 Then

17. Good morning, Charles –

    Yep, there is an elusive VB-tag bug. It treats less-than/greater-than pairs in that order as HTML markers and ignores/throws away everything between.

    In your case, it just discarded the not-equals I think. In other instances, when the less-than is lines away from the greater-than, it will shorten your code for you. Quite helpful that way. ;-)

    The workarounds are to use things like LT, LE, GT, GE and !=, or post the code not inside the VB tags. I always give a warning about angle-bracket substitutions, and trust the IDE to flag those instances. A pita, I know. It doesn’t show up in normal posts.

    ..mrt

18. one more: =SUM(LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,””},””))*-{1,2,3,4,5,6,7,8,9,-45})

    don’t know how it compares in speed, but is efficient in that it doesn’t depend on other ranges so only recalculates when A1 changes. lhm

19. Lori: That’s slick. It works with positive and negative numbers, with an empty cell, and with decimal numbers. It evens adds the numbers in a string that contains non-numeric characters. Of course, whether that’s good or bad is not clear…

20. If you are willing to accept an error being generated if a cell does not contain a real number (whether as text or as an actual number), 12AB34 as an example, and if you textual numbers are no more than 29 significant digits long, then this one-liner UDF should work…

    Function SumDigits2(S As Variant) As Long
    If S “” Then SumDigits2 = Evaluate(Format(Replace(S, “.”, “”), “0? & Replace(String(28, “x”), “x”, “+0?)))
    End Function

21. Let’s try that formula again, this time using .ne. in place of the “not equal” sign (less than symbol followed by greater than symbol) that the Comment Processor “ate” (so if copy/pasting this, make the substition back to the “not equal” sign)…

    Function SumDigits2(S As Variant) As Long
    If S .ne. “” Then SumDigits2 = Evaluate(Format(Replace(S, “.”, “”), “0? & Replace(String(28, “x”), “x”, “+0?)))
    End Function

22. Function SumDigits2(S As Variant) As Long
    If S NEQ “” Then SumDigits2 = Evaluate(Format(Replace(S, “.”, “”), Replace(string(len(s),“x”), “x”, “+0”)))
    End Function

23. Hans,

    Your function will not be able to handle more than 29 total (text) digits because of a limitation in the Format function… it appears to use the Decimal sub-type of the Variant data type to process its first argument and the Decimal sub-type is limited to a maximum of 29 digits total digits providing there is no decimal point (which is the reason for the first Replace function call). For example, try this macro out with your function and you will see the problem (displayed in the Immediate Window)…

    Sub test()
      Dim X As Long, TestNumber As String
      For X = 1 To 3
        TestNumber = “123456789012345678901234567” & Mid(“890”, 1, X)
        Debug.Print “Sum “ & TestNumber & ” digits is “ & SumDigits2(TestNumber)
      Next
    End Sub

    The first two digit sums will be correct but the third one (where only a zero is being affixed to the end of the second one) will be obviously incorrect. However, I do note your elimination of the concatenation of the leading zero in favor of including it inside the second Replace function call is a good way to simplify the function further…

    Function SumDigits2(S As Variant) As Long
      If S  “” Then SumDigits2 = Evaluate(Format(Replace(S, “.”, “”), Replace(String(29, “x”), “x”, “+0”)))
    End Function

24. Arrgh! I did it again… I forgot about the “not equal” sign problem…

    Function SumDigits2(S As Variant) As Long
      If S  “” Then SumDigits2 = Evaluate(Format(Replace(S, “.”, “”), Replace(String(29, “x”), “x”, “+0”)))
    End Function

25. What! I did it again!! Okay, I changed the test so that I can’t screw it up again ;-)

    Function SumDigits2(S As Variant) As Long
      If Len(S) Then SumDigits2 = Evaluate(Format(Replace(S, “.”, “”), Replace(String(29, “x”), “x”, “+0”)))
    End Function

26. Rick,

    to avoid limits and ‘impossible’ tests:

    Function SumDigits2(S As Variant) As Long
     c1 = Replace(String(29, “+”), “+”, “+0”)
     For j = 1 To Len(Replace(S, “.”, “”)) Step 29
       SumDigits2 = SumDigits2 + Evaluate(Format(Mid(Replace(S, “.”, “”), j, 29), c1))
     Next
    End Function

27. I wrote my original function more as an interesting case because it was a one-liner solution. If you want to go to multiple lines and, especially, a loop solution, then I would use the code Charles Williams posted earlier… the Byte array method would be among the fastest possible loop solutions that could be constructed.

Posting code? Use <pre> tags for VBA and <code> tags for inline.