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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.



AMD



For example as per above pic, I have few entries in B3 like Auto 50, Lunch 200, Dinne (sorry for spelling mistake) 150 so I want to add sum of 50+200+150 in C3. So C3 should show 400. Same for C4 where it should result as 800.



It should also calculate negative numbers. Like if cell have 50 & -20 so result should show 30.



It is possible to have any such formula without Macro so I can use that file on my Android phone as well?
 
Upvote 0
It would be far better for you to design your spreadsheet to have multiple pieces of data in one cell
Column B type, column C amount and then two columns would look like
Auto 50
Lunch 200
Dinne 150 and a simple SUM or SUMIF would get the sum you need.

If your Android phone doesn't handle array formulas, then this is the only option that I can see.
 
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.

For example as per above pic, I have few entries in B3 like Auto 50, Lunch 200, Dinne (sorry for spelling mistake) 150 so I want to add sum of 50+200+150 in C3. So C3 should show 400. Same for C4 where it should result as 800.

It should also calculate negative numbers. Like if cell have 50 & -20 so result should show 30.

It is possible to have any such formula without Macro so I can use that file on my Android phone as well?
I cannot tell you if it will work on an Android phone or not, but this formula does work on an Excel worksheet (as long as your data always looks like shown... comma delimiter, number at the end of field with a space in front of it)...

=SUMPRODUCT(0+TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",200)),ROW(1:3)*200,200))," ",REPT(" ",200)),200)))
 
Upvote 0
jimblimm


my contribution


=SUM(IFERROR(1*MID(D6,ROW($1:$99),1),))


press CTRL + SHIFT + ENTER




Decio
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top