## Excel Challenge - Adding the digits of a number

amar.cbs
Posts: 3
Joined: Wed Jul 04, 2012 6:07 pm

### Excel Challenge - Adding the digits of a number

Hello everybody,

I have an excel challenge. An excel formula is required to add the digits of a number.

For example,
a) If the number is 231, then answer should be 2+3+1 = 6
b) If the number is 45, then answer should be 4+5 = 9
c) Similarly if the number is 5731, then answer should be 5+7+3+1 = 16

The formula should be dynamic to fit into any of the above cases.

Give it a try - we tend to learn while doing funny things.

Kind regards,
Amardeep

Tags:

Posts: 12
Joined: Sat Feb 11, 2012 7:35 pm

### Re: Excel Challenge - Adding the digits of a number

Amardeep,

I posted this question to some of our Mumbai Financial Modelling faculty, a thread of replies ensued in my inbox!

Here are some of those replies -

Vikram Pandya writes -
These can be done in many ways, one of them is given below

Function SumDigits(MyNum)
For i = 1 To Len(MyNum)
MySum = MySum + CInt(Mid(MyNum, i, 1))
Next
SumDigits = MySum
End Function

Note that the above function is without any validation rule. i.e. On String or Decimal Values you will get #Value! error

Anil Bains writes -
SUMPRODUCT(MID(\$C\$3,ROW(OFFSET(\$C\$1,,,LEN(C3))),1)*1)

What about you folks? There are multiple ways to do this!

amar.cbs
Posts: 3
Joined: Wed Jul 04, 2012 6:07 pm

### Re: Excel Challenge - Adding the digits of a number

Thanks Vikram and Anil. These are quite useful.

Here is my solution. Let's say cell A1 contains the number, then the formula would be:

=SUM(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)

The above formula works only when the number is positive and without decimal places.

Here is an alternate comprehensive formula which takes care of even negative numbers and decimal values:

=SUM(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,0))

Kind regards,
Amardeep