How to Sum Digits of a Number

The roots of this blog post come from the following conversation from one of my training sessions

Participant : “Chandeep, the other day I went with my wife to buy a new mobile number. Since my wife is a bit finicky about astrology, she requested the shopkeeper to show us mobile numbers totaling to 9. The shopkeeper, along with his dirty looks sent me a dump of numbers and asked me to do it myself”

Me : I was trying to come up with a formula while he sharing his ordeal of manually totaling up phone numbers

Let’s dedicate this blog post to that lady and to everyone else who needs a total of digits

Assume some numbers

Here is the formula to extract each digit and sum them

=SUM(VALUE(MID(B3,ROW(INDIRECT('1:'&LEN(B3))),1)))

A few things to Note

B3 is the cell address where the first number is kept
This formula will only total once. For instance the first number ‘9899830308’ totals to 57
- If you want to further total 57 (i.e 5+7 = 12), until you reach a single digit, you’ll have to drag the formula to the right

An Alternative Way – VBA Code

Here is a short VBA Code that will take total until the number is converted to a single digit

Sub TakeTotals()
cnt = Range('B1').CurrentRegion.Rows.Count
Range('c1:c' & cnt).Clear
'for counting how many numbers are there in a range


For countloop = 1 To cnt
'the loop will run n numbers in the range

 For i = 1 To Len(Range('B' & countloop))
 extnum = Mid(Range('B' & countloop), i, 1)
 Range('c' & countloop) = extnum + Range('c' & countloop)
 Next i
 'a second loop for extracting and adding each number
 
 Do Until Len(Range('c' & countloop)) = 1
 firstnum = Mid(Range('c' & countloop), 1, 1)
 secondnum = Mid(Range('c' & countloop), 2, 1)
 Range('d' & countloop) = firstnum
 Range('e' & countloop) = secondnum
 Range('c' & countloop) = Range('d' & countloop) + Range('e' & countloop)
 Loop
 'a 3rd loop for repeating the process until the sum turns into a single digit
 
Next countloop
Range('d1:e' & cnt).Clear
End Sub

Note a few things to customize the code

B1 (in bold and red) is the cell where your first number is kept. Please customize it accordingly
B is the column address where the numbers are kept

DOWNLOAD EXCEL FILE FROM DOWN BELOW (WITH BOTH METHODS)