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

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

### 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