# 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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### starl

assume the numbers are in A1
Code:
``<code>=SUMPRODUCT(MID(a1,ROW(OFFSET(\$A\$1,,,LEN(a1))),1)+0)``
</code>

#### jimblimm

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

#### Rick Rothstein

##### MrExcel MVP
Give this formula a try...

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

#### T. Valko

##### Well-known Member

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:

#### SachinBhanagle

##### New Member
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.