Calculating Sum of Digits in a Number using Array Formulas

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 58.

How to get the sum of digits?

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.