sum all digits in single cell

jimblimm

Board Regular
Joined
May 11, 2012
Messages
219
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>
 
Upvote 0
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
 
Upvote 0
Give this formula a try...

=SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
 
Upvote 0
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:
Upvote 0
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.