sum all digits in single cell

[jimblimm]

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

 

[mikerickson]

That does not add up the whole numbers, rather, it adds up all of the individual digits.

That's one of the reasons that hijacked necro-threads are a bad idea.
The 2012 OP was about adding individual digits.
SachinBhanagle's 2016 diversion was about whole numerals.

 

[Rick Rothstein]

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)))

I posted the wrong formula (that was my specific test formula, not the generalized one meant to handle any number of items), here is the generalized one (as written, it works for cells containing up to 300 characters)...

=SUMPRODUCT(0+TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",300)),ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))*300,300))," ",REPT(" ",300)),300)))

 

[SachinBhanagle]

I posted the wrong formula (that was my specific test formula, not the generalized one meant to handle any number of items), here is the generalized one (as written, it works for cells containing up to 300 characters)...

=SUMPRODUCT(0+TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",300)),ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))*300,300))," ",REPT(" ",300)),300)))

Thank you so much.... I think I need some modification in as well as when I apply this formula to all below cells, it gives #Value and then the last column where I should get sum of all cells does not give any value. I will find a way. Thanks

 

[SachinBhanagle]

I posted the wrong formula (that was my specific test formula, not the generalized one meant to handle any number of items), here is the generalized one (as written, it works for cells containing up to 300 characters)...

=SUMPRODUCT(0+TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",300)),ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))*300,300))," ",REPT(" ",300)),300)))

I have modified it with IF condition as below.

=IF(B3=0,0,SUMPRODUCT(0+TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&B3,",",REPT(" ",300)),ROW(INDEX(B:B,1):INDEX(B:B,1+LEN(B3)-LEN(SUBSTITUTE(B3,",",""))))*300,300))," ",REPT(" ",300)),300))))

This is what I was looking for and thank you, thank you so muchhhhhh.

I have zero knowledge of Excel and never used it, so you can understand how helpful it was for me.

Though I did not understand how it works but I will search on Internet to understand your formula.

If possible can you please decode or explain it to me?

 

[deciog]

That does not add up the whole numbers, rather, it adds up all of the individual digits.

Sorry, I answered the original question of this topic is "sum all digits in single cell" not the issue acrecentada this topic, and the formula is fully correct "=SUM(IFERROR(1*MID(D6,ROW($1:$99),1),))".


Decio

 

[SachinBhanagle]

I posted the wrong formula (that was my specific test formula, not the generalized one meant to handle any number of items), here is the generalized one (as written, it works for cells containing up to 300 characters)...

=SUMPRODUCT(0+TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",300)),ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))*300,300))," ",REPT(" ",300)),300)))

I got a new formula, a bit lengthy one but worth of it.

=SUMPRODUCT((TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(B3),"A"," "),"B"," "),"C"," "),"D"," "),"E"," "),"F"," "),"G"," "),"H"," "),"I"," "),"J"," "),"K"," "),"L"," "),"M"," "),"N"," "),"O"," "),"P"," "),"Q"," "),"R"," "),"S"," "),"T"," "),"U"," "),"V"," "),"W"," "),"X"," "),"Y"," "),"Z"," "),","," "))," ",REPT(" ",255)),1+(ROW($A$1:$A$1002)-1)*255,255)) & "0")/10)

This formula will work in all circumstances even if the numbers precede text or if there is no space between text & numbers whereas your formula may not work in these scenarios.

 

[mytrafun]

Hello guys,

But how to calculate this:

2*1.0+1.5+2*2.5+4.0 if I have it in one cell

<tbody>
</tbody>

 

[Rick Rothstein]

But how to calculate this:

2*1.0+1.5+2*2.5+4.0 if I have it in one cell

<tbody>
</tbody>

Here is a UDF (user defined function) that you can use...

Function Calc(S As String) As Double
  Application.Volatile
  Calc = Evaluate(S)
End Function

Note: I made the function Volatile so that if you have cell references in the expression in the cell you pass into the function and you change the the value in the referenced cell, the function will recalculate automatically.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Calc just like it was a built-in Excel function. For example,

=Calc(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[mikerickson]

Another way uses Names.
If the string "2*1.0+1.5+2*2.5+4.0" is in A1.
Select some other cell and define the name NameEval RefersTo: =EVALUATE(!A1)
Then put =NameEval in the same cell that was active when you entered the name definition.

 

[mytrafun]

Here is a UDF (user defined function) that you can use...

Function Calc(S As String) As Double
  Application.Volatile
  Calc = Evaluate(S)
End Function

Note: I made the function Volatile so that if you have cell references in the expression in the cell you pass into the function and you change the the value in the referenced cell, the function will recalculate automatically.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Calc just like it was a built-in Excel function. For example,

=Calc(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thanks!!!!! Works just perfect!!!!