# sum all digits in single cell

#### jimblimm

##### Board Regular
is there a way to sum digits in a single cell.

in a1
4567

result in b1
22

4+5+6+7
i know i could split the single cell a1 into 4 cells then add, but is there a formula to bypass doing so

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
assume the numbers are in A1
Code:
``<code>=SUMPRODUCT(MID(a1,ROW(OFFSET(\$A\$1,,,LEN(a1))),1)+0)``
</code>

what if a1 is a formula that calculates a value

ex a1

if(b1="","",countif(b10:b2000,"s")
which equals 4567

its not giving the right answer

but if i manually type 4567 in it does

Give this formula a try...

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

what if a1 is a formula that calculates a value

ex a1

if(b1="","",countif(b10:b2000,"s")
which equals 4567

its not giving the right answer

but if i manually type 4567 in it does
You might want to test that cell A1 contains a number first:

=IF(COUNT(A1),SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)

Last edited:
I want to know how to sum value of one cell without using Macro. Macro & VBA can not be used on Android devices.

Actually I am trying to maintain a excel sheet to manage and calculate my expenses.