Here is a fun formula to write.
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. 😛
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).
50 Responses to “Calculating Sum of Digits in a Number using Array Formulas [for fun]”
=SUMPRODUCT(1*MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1))
HI,
We also use double mines mart befor mid like --
=SUMPRODUCT(--MID(B4,ROW(INDIRECT(“1:”&LEN(B4))),1))
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
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
*Sorry: "...trying to calculate the value of a cell containing..."
I'm not a complete idiot, I just play one on the Internet.
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
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)
My offering
=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)and
=A1 - FLOOR(A1 - 1, 9)How do you solve the same question for decimal numbers e.g. 1,142367?
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)
WRONG,
the single digit sum of 23 is 5 but =MOD(23,9) would equal 3
@Hipo
Mod(23, 9) = 5
Mod(23, 9) = 23-18 = 5
But Mod(18, 9) = 0, while 1+8 = 9
Mod(n-1,9)+1
For n = 23,
Mod(23-1,9)+1 = 5
For n=18
Mod(18-1,9)+1 = 9
@Leszek
IF( AND( MOD(A1,9)=0 ; A1>=9 ) ; 9 ; MOD(A1,9) )
It works great 🙂
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
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.
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
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)
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))
Hi Chandoo
Plz Check mail with subject "Numeric to work convertor" in your gmail account and do needful..
Thanks
Istiyak
=SUM(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)Ignore my respond mail lol
George
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.....!!
@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
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!
@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
@Hui Thank you very much for the explanation. Helps to clear the confusion.
@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)"
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)
@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....
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).
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.
@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!
[...] Calculate sum of digits in a number using Array formulas [...]
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.
[...] Comment by Cameron & subsequent discussion on how to convert binary numbers to decimal [...]
[...] 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 [...]
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?
hi chandoo,
do u have an answer for the bonus question?
thanks.
To find single digit sum, use =MOD(number, 9)
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))
=MID(B4,1,1)+MID(B4,2,1)+MID(B4,3,1)
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)}
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.
Dear all,
I want to bring each digit of a number to a power, then to sum up all the power digits within this number. If for example my number is:
2345, I want to calculate the following: (2^2)+(3^2)+(4^2)+(5^2)
Can this calculation be done in excel ?
Many thanks.
Eddie
@Eddie
Yes, Yes it can be done in Excel
Assuming your value is in cell A1
=SUMPRODUCT(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),1)^2)
will return 54
'USAGE: =SUMALLDIGITS(A2)
'USAGE FOR BONUS Question: RECURSE FUNCTION n TIMES
Public Function SUMALLDIGITS(rngCell As Range) As Byte
On Error GoTo ERRH
'PARSED DIGIT FROM INPUT
Dim sSingleDigit As String
'TEMP SUM OF ALL PARSED DIGITS
Dim lSum As Byte
'LOOP COUNTER
Dim i As Byte
'IF INPUT IS NULL, RETURN 0
If Len(rngCell) = 0 Then
GoTo BADINPUT
End If
'IF INPUT SINGLE DIGIT, RETURN INPUT
If Len(rngCell) = 1 Then
GoTo RETURNINPUT
End If
If Len(rngCell) > 1 Then
'RETURN 0 IF ALL INPUT NON-NUMERIC
lSum = 0
'BEGIN PARSING OF INPUT
For i = 1 To Len(rngCell)
'ISOLATE SINGLE DIGIT STARTING FROM RIGHT TO LEFT
sSingleDigit = Left(Right(rngCell, i), 1)
'IGNORE NON-NUMERIC CHARACTERS
If IsNumeric(sSingleDigit) And sSingleDigit "0" Then
'ADD DIGIT TO TEMPVAR lSum
lSum = lSum + CByte(sSingleDigit)
End If
Next i
SUMALLDIGITS = lSum
GoTo CLEANEXIT
End If
RETURNINPUT:
SUMALLDIGITS = rngCell
GoTo CLEANEXIT
BADINPUT:
SUMALLDIGITS = 0
GoTo CLEANEXIT
ERRH:
If Err.Number 0 Then
MsgBox Err.Description
SUMALLDIGITS = -1
Err.Clear
GoTo CLEANEXIT
End If
CLEANEXIT:
Exit Function
End Function
KINDLY SEE THIS :
https://exceltutorialworld.com/sum-of-digits-in-a-cell-in-excel/
BEST REGARDS
HELAL
=SUM(NUMBERVALUE(MID(D4,SEQUENCE(LEN(D4)),1)))
=SUM(MID(B4,SEQUENCE(LEN(B4)),1)*1)