Celebrate 'The VLOOKUP Book' birthday with us. Get 50% discount on the e-book today & tomorrow (30 & 31 October only).

Click here for details

Calculating Sum of Digits in a Number using Array Formulas [for fun]

Posted on March 18th, 2011 in Excel Howtos - 42 comments

Here is a fun formula to write.

Sum of Digits in a number - how to calculate?Given a number in cell, I want you to find the sum of digits in it. So, for eg. if you have the number 3584398594 in a cell, the sum would be =3+5+8+4+3+9+8+5+9+4, equal to 1994. :P

I am kidding of course, the sum would be 58.

Now, how would you write a formula to find this sum automatically based on the number entered in the cell?

Go ahead and figure it out. If you can, come back and check your answer with mine below.

How to get the sum of digits?

In order to get the sum of digits, we just need to separate and add all the numbers. Sounds simple right? But how!?!

Very simple, we use Array formulas and pixie dust.

First the formula:

Assuming the number is in cell B4, we write the formula,

=SUMPRODUCT(MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0)

to get the sum of digits.

Note: you need not press CTRL+SHIFT+Enter to get this formula work.

How does this formula work?

We will go inside out to understand this formula.

The portion – ROW(OFFSET($A$1,,,LEN(B4))): Gives the numbers 1,2,3…n where n is the number of digits of the value in B4.

The portion – MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1): Now gets the individual digits of the number and returns them as array (since the 2nd argument of MID formula is an array.

The SUMPRODUCT: is the pixie dust. It just magically sums up all the digits extracted by MID(). We use a +0 at the end because MID() returns text that needs to be converted to numbers for SUMPRODUCT to work its magic.

How would you have solved this?

I just love SUMPRODUCT Formula. So I use it whenever I can. But you may like other techniques. So please tell me how you would solve this problem using formulas. Post your formula using comments.

Note: while posting your formula, just put it between CODE tags like this:

<code>your formula goes here</code> so that it gets displayed correctly.

Bonus question: How to calculate single digit sum of the digits?

Go ahead and solve it too.

The single digit sum is arrived by summing the sum of digits of sum of digits of … of a number. For ex. the single digit sum for number 3584398594 is 4 (because the sum of digits is 58, whose sum of digits is 5+8 = 13, whose sum of digits is 1+3 =4 and we stop here because 4 is a single digit number).

More Formula Fun:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

42 Responses to “Calculating Sum of Digits in a Number using Array Formulas [for fun]”

  1. sam says:

    =SUMPRODUCT(1*MID(B4,ROW(INDIRECT(“1:”&LEN(B4))),1))

  2. Stef@n says:

    an in German ;)
    Array-Formula
    {=SUMME((TEIL(A1;ZEILE(INDIREKT(“1:”&LÄNGE(A1)));1)*1))}

    as a formula
    =SUMMENPRODUKT((0&TEIL(A1;SPALTE(1:1);1))*1)

    and as a function
    Function QUERSUMME(zahl)
    Dim i%
    For i = 1 To Len(zahl)
    QUERSUMME = QUERSUMME + Val(Mid(zahl, i, 1))
    Next i
    End Function

  3. D says:

    Quick similar question for anyone who sees this!

    I’m trying to do something similar but instead of summing digits I’m trying to calculate a sum of a text containing the text form of various formulas such as 2+4, 5*10, 6/45.

    I could use EVALUATE in VBA but I’m wondering if there is some trick to using SUMPRODUCT to pick apart the individual pieces and evaluate the formula.

    Any thoughts?!
    Thanks

  4. D says:

    *Sorry: “…trying to calculate the value of a cell containing…”

    I’m not a complete idiot, I just play one on the Internet.

  5. Tristan says:

    Hi,
    For the sum of digits, one could use the index function like:
    =SUM(INDEX(1*(MID(B2,ROW(INDIRECT(“1:”&LEN(B2))),1)),,))
    For the bonus question I like:
    =IF(MOD(B2,9)=0,9,MOD(B2,9))
    Thanks,
    Tristan

  6. Stef@n says:

    And for the Bonus-Question:

    Function QuerSumme(Zelle As Range)
    Dim Dummy As String
    Application.Volatile
    Dummy = Zelle.Value
    While Len(Dummy) > 1
    QuerSumme = 0
    For i = 1 To Len(Dummy)
    QuerSumme = QuerSumme + Mid(Dummy, i, 1) * 1
    Next
    Dummy = QuerSumme
    Wend
    End Function
    CALL
    =QuerSumme(A1)

  7. oldchippy says:

    My offering

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

    and

    =A1 - FLOOR(A1 - 1, 9)

  8. Hugo Uvin says:

    How do you solve the same question for decimal numbers e.g. 1,142367?

  9. Rick Rothstein (MVP - Excel) says:

    The answer to the Bonus Question turns out to be quite simple (it’s known in mathematical circles as “casting out nines”)…

    =MOD(A1,9)

  10. Rick Rothstein (MVP - Excel) says:

    For those who might want to see a UDF for your first question (sum the digits), it can be done in a single line of code…

    Function SumDigits(S As String) As Long
    SumDigits = Evaluate(Format(S, Replace(String(Len(S), “X”), “X”, “+0″)))
    End Function

  11. Abbas says:

    Chandoo,

    Please help. I don’t understand this formula. The portion ROW(OFFSET($A$1,,,LEN(B4))) results in only 1. How do you see 1, 2, 3, etc? Could you please explain this formula in more detail?

    Thank you.

  12. Michael Pennington says:

    I couldn’t figure out any way to solve the main question that wasn’t essentially the same as the previously listed methods. But Rick’s bonus answer inspired to create a different, (albeit, uglier) way to solve the bonus. Brilliant work by the way everybody, I learn a lot from Chandoo and a lot from the brilliant comments, as well.
    My bonus formula:

    =IF(MID(B4/9,3,1)="",9,MID(B4/9,3,1))+0

  13. Rick Rothstein (MVP - Excel) says:

    Hey Michael, your posting made me rethink what I had posted and, damn, I forgot about the 9′s becoming 0 instead of staying at 9. My revised and now working correctly forumula
     
    =1+MOD(A1-1,9)

  14. Brendan says:

    Not half as elegant, but as an alternative (assuming the number is in cell I5):


    =SUM(MOD(INT(I5/10^( CEILING(LOG(I5),1)- ROW(INDIRECT("a1:a"&(CEILING(LOG(I5),1)))) ) ),10))

  15. Istiyak says:

    Hi Chandoo
    Plz Check mail with subject “Numeric to work convertor” in your gmail account and do needful..
    Thanks
    Istiyak

  16. George says:

    =SUM(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)

    Ignore my respond mail lol

    George

  17. Dhirendra Kum says:

    I tried hard and hard and hard and come to the following code

    ^*%#%^)(@ Just kidding

    You are doing really good job, and I am still learning…..!!

  18. Hui... says:

    @Abbas
    re: ROW(OFFSET($A$1,,,LEN(B4)))

    This creates a single dimension array starting at A1 and extending vertically to the length of the number. The row then assigns the row number to each array element
    Note that it doesn’t actually put values in A1:A7 etc, A1 is just a place holder to anchor the array to.

    Mid then extracts the corresponding character from the Number using the row number from the array as the Start position.

    Finally Sumproduct adds it all up

    I Hope that helps a bit

  19. Daro says:

    Excelent. Just one question… I find it weird that you don’t need to enter the formula as an array formula. Does it have anything to do with the fact that the SUMPRODUCT formula asks for arrays as parameteres? If so, why is that?

    Thnx!

  20. Hui... says:

    @Daro,
    Spot on
    Sumproduct is looking for and recieving an array of data
    and so it just does its bit on the array of data as supplied

  21. Abbas says:

    @Hui Thank you very much for the explanation. Helps to clear the confusion.

  22. Siddharth says:

    @George: Minor change to George’s solution. Using Sum instead of Sumproduct and Pressing Shift+Cntl+Enter “SUM(MID(B2,ROW(INDIRECT(“1:”&LEN(B2))),1)+0)”

  23. Cameron says:

    This made me think of a different way to convert binary to decimal that works up to 15 bits instead of 9 like the BIN2DEC() formula (although I think Excel 2010 works at higher limits?)
    Anyway, here’s the formula! (Binary value is in B1)
    =SUMPRODUCT(2^(LEN(B1)-ROW(OFFSET($A$1,,,LEN(B1)))),MID(B1,LEN(B1)+1-ROW(OFFSET($A$1,,,LEN(B1))),1)+0)

    • Chandoo says:

      @Cameron… Wow that is a beautiful formula… Thank you so much for sharing it. Here is your donut :)

      Btw, you can set formatting of B1 to Text, and that way this formula would work up to any number of digits, not just 15….

  24. Rick Rothstein (MVP - Excel) says:

    For both Cameron and Chandoo
    ——————————————————————————————————-

    @Cameron,

    Your formula does not work correctly… try putting 1000 or 1010 in B1 to see the problem. The reason it is not working correctly is because you are stripping off the digits in B1 backwards. While subtracting the row values from the length of B1′s entry is correct for the powers of 2, it is not the correct thing to do for the position argument of the MID function, rather, you need to use the row values directly there. Here is your corrected formula

    =SUMPRODUCT(2^(LEN(B1)-ROW(OFFSET($A$1,,,LEN(B1)))),MID(B1,ROW(OFFSET($A$1,,,LEN(B1))),1)+0)

    ——————————————————————————————————-
    @Chandoo,

    Even formatting B1 as Text, there is still a limit on the size of the binary value that can be converted by this formula. The limit seems to be 36 or 37 bits depending on the arrangements of the 1′s and 0′s… the limit is in how big a number the formula can display before it rolls over into scientific notation (at which point the generated answer ceases to be accurate).

  25. Rick Rothstein (MVP - Excel) says:

    Just to follow up on my last posting, specifically referring to Chandoo’s statement about the length of binary digits the formula can process. As I said, the formula is restricted to being able to handle a 36 or 37 bit binary value before accuracy is lost. Now, I would think being about to convert a 36/37 bit binary number to decimal should cover most people’s needs, but there are always exceptions ;-) so here is a copy of a message that I have posted in the old newsgroups in the past that contains a UDF (user defined function) that can handle very large binary digits…

    Below is a UDF that will handle up to a 96-bit binary number (decimal value 79228162514264337593543950335) which I’m guessing is way more than you will ever need. The code is efficient (looping only as many times as necessary to process the passed in binary value), so don’t worry about it being able to handle such a large binary value. The function returns a real numeric value up to 9999999999 after which it returns text representations of the calculated number.

    Function BinToDec(BinaryString As String) As Variant
    Dim X As Integer
    Const TwoToThe48 As Variant = 281474976710656#
    For X = 0 To Len(BinaryString) – 1
    If X > 48 Then
    BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
    Len(BinaryString) – X, 1)) * _
    TwoToThe48 * CDec(2 ^ (X – 48))
    Else
    BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
    Len(BinaryString) – X, 1)) * CDec(2 ^ X)
    End If
    Next
    If Len(BinToDec) > 10 Then BinToDec = CStr(BinToDec)
    End Function

    On the assumption that you do not know how to install a UDF, here are the quite simple instructions. From any worksheet, press Alt+F11 which will take you into the VB editor. Once in the VB editor, click Insert/Module on its menu bar. A code window will open up… simply paste the code above into that code window. That’s it… you are done. You can now use the BinToDec function on your worksheet just like it was a built-in function. So, if your binary value is in A1, then =BinToDec(A1) will display the decimal equivalent of it.

  26. Cameron says:

    @Rick: Good catch! Indeed I didn’t do any error checking, I just threw that together rather hastily and the reverse sequence found it’s way to both sides. Whoops! I also noticed the limitation last night on the value of the binary. I never thought I’d have the need to return binary values that large either, but nonetheless began to build my own UDF to convert any base to another (though I never finished adding in bases over 10. Just been busy :))

    @Chandoo: The beautiful part was using the ROW()/OFFSET() to generate the sequence of numbers in a SUMPRODUCT! Thank you for adding it to my repertoire!

  27. Hui... says:

    I recieved an email recently requesting a description of how the formula =SUMPRODUCT(MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0)
    Works

    I figure it is worth posting here as if one person bothered to ask there is bound to be others who didn’t

    This technique uses a technique generally at a higher level than regularly used at Chandoo.org but just the same it is useful and worth learning.

    Using B4: 16548
    and =SUMPRODUCT(MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0)

    When pulling apart formulas you can start on the outside, inside or do a bit of both, which is what I’ll do.

    Sumproduct() Sums the Product of it entries, that is it Multiplies all the components together and then Sums those for each entry in the input range/array
    Your imediate response should be that there isn’t an input Range or Array?
    Sort of correct, which means sort of incorrect

    The only component in Sumproduct is MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0
    we can ignore the +0 as that is just making sure that the MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1) bit evaluates to True/False if required.

    So what does MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1) do?
    Mid is a Text Function that takes a String from within another string
    In this case it is taking 1 character “,1)” from the ROW(OFFSET($A$1,,,LEN(B4))) position of the String at B4 (our Number)

    Now we get to ROW(OFFSET($A$1,,,LEN(B4))) which is the position in String B4

    Row takes returns the Row number from a Range
    and the Range is OFFSET($A$1,,,LEN(B4))

    Now here is the tricky bit
    OFFSET($A$1,,,LEN(B4)) says setup a Range, starting at A1, with no Row or Column Offset which is Len(B4) high, the Length of our Number in B4 which is 5.
    So it is effectively saying OFFSET($A$1,,,LEN(B4)) = (A1:A5)
    Now it doesn’t actually setup a Range in that area it just references it, because all it is doing is returning the Row Number for each Cell in the Range

    Now the real tricky bit is that Sumproduct is an Array Formula, except in some cases like this and normal Ranges you don’t need to Ctrl Shift Enter as it does it automatically.
    So what this means is that it procesesses every cell in the Input Range, which in this case is our reference range A1:A5

    It Returns the Row Number which is 1,2,3,4,5

    And then uses these to extract the middle 1 character from B4 position 1,2,3,4,5 and
    Adds them up.

    So in our case it extracts from Position 1 value 1, pos 2 value 6, pos 3 value 5, pos 4 value 4 and pos 5 value 8 and Sums them to get 21.

  28. [...] Comment by Cameron & subsequent discussion on how to convert binary numbers to decimal [...]

  29. [...] and how to use it Advanced SUMPRODUCT Queries Use Array Formulas to check if a list is sorted Calculating sum of digits in a number using formulas Check if a number is Prime using array formulas More… Excel Array Formulas – Examples [...]

  30. Joshua says:

    I’m working on a similar issue, but am doing something with language characters.

    Here’s the deal, briefly outlined.

    A1 = “cat”

    C1:D3 had the following:

    a 13
    c 25
    t 33

    The formula would need to do a simultaneous search for values, and then it would have to return the sum of those values. Is there a way in which one could adapt the formula to do that kind of move?

  31. mintintense says:

    hi chandoo,
    do u have an answer for the bonus question?
    thanks.
     

  32. tj says:

    I don’ think so:
    Single digit sum of 9 is 9, but MOD(9,9) is 0
    You need to check for multiples of 9, e.g. =IF(MOD(A25;9)=0;9;MOD(A25;9))

  33. CHERRY JORDAN says:

    =MID(B4,1,1)+MID(B4,2,1)+MID(B4,3,1)

  34. John Ness says:

    I realize I’m late to the party, but I only found this site yesterday. It seems a bunch of people are using Offset or Indirect and, though they work, are volatile and can cause unforeseen issues, such as slowing the workbook down if they’re used a lot. Here’s my formula, which uses Index to allow for up to a 15-digit number to have its digits added: {=SUM(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)+0)}

  35. Kerry Millen says:

    Put the array formula

    {=SUM(–MID(B4, ROW(INDIRECT(“1:” & LEN(B4))), 1))}

    in cells C4 (assuming the first number is in cell B4) through C13.

Leave a Reply