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

How to Sum Digits of a Number 2

Here is the formula to extract each digit and sum them

How to Sum Digits of a Number 3

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

 

A few things to Note

  1. B3 is the cell address where the first number is kept
  2. 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

How to Sum Digits of a Number 4

 

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

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

 

DOWNLOAD EXCEL FILE (WITH BOTH METHODS)

 

Other Helpful VBA Codes and Formulas

  1. How to Inverse your Data – Formula
  2. Consolidate Data from all Sheets – VBA Code
  3. Create an Index from Sheet Names
  4. Create dependent Data Validation Dropdowns – Formula
  5. 7 Date Formulas that will make your life easy

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI