Summing the digits in Excel cells (long and short strings)

Question

I'm working on a research related to frequencies.

I want to sum all the numbers in each cell and reduce them to single number only.

some cells have 2 numbers, others have 13 numbers. like these..

24.0542653897891
25.4846064424057
27
28.6055035477009

I tried several formulas to do that. the best ones have me 2 digits number, that I couldn't sum it again to get a single result.

like these Formulas:

=SUMPRODUCT(MID(SUBSTITUTE(B5,".",""),ROW(INDIRECT("1:"&LEN(B5)-1)),1)+0)
=SUMPRODUCT(1*MID(C5,ROW(INDIRECT("1:"&LEN(C5))),1))

any suggestion? Thank you in advance.

Answer 1

EDIT

Based on your explanation your comments, it seems that what you want is what is called the digital root of the all the digits (excluding the decimal point). In other words, repeatedly summing the digits until you get to a single digit.

That can be calculated by a simpler formula than adding up the digits.

=1+(SUBSTITUTE(B5,".","")-1)-(INT((SUBSTITUTE(B5,".","")-1)/9)*9)

For long numbers, we can split the number in half and process each half. eg:

=1+MOD(1+MOD(LEFT(SUBSTITUTE(B5,".",""),INT(LEN(SUBSTITUTE(B5,".",""))/2))-1,9)+1+MOD(RIGHT(SUBSTITUTE(B5,".",""),LEN(SUBSTITUTE(B5,".",""))-INT(LEN(SUBSTITUTE(B5,".",""))/2))-1,9)-1,9)

However, the numbers should be stored as TEXT. When numbers are stored as numbers, what we see may not necessarily be what is stored there, and what the formula (as well as the UDF) will process.

The long formula version will correct all the errors on your worksheet EXCEPT for B104. B104 appears to have the value 5226.9332653096000 but Excel is really storing the value 5226.9333265309688. Because of Excel's precision limitations, this will get processed as 5226.93332653097. Hence there will be a disagreement.

Another method that should work would be to round all of the results in your column B to 15 digits (eg: . Combining that with using the long formula version should result in agreement for all the values you show.

Explanation

• if a number is divisible by 9, its digital root will be 9, otherwise, the digital root will be n MOD 9

• The general formula would be: =1+Mod(n-1,9)

  • In your case, since we are dealing with numbers larger than can be calculated using the MOD function, we need to both remove the dot, and also use the equivalent of mod which is n-(int(n/9)*9)
• Notes:
  • this will work best with numbers stored as text. Since Excel may display and/or convert large numbers, or numbers with many decimal places, differently than expected, working with text strings of digits is the most stable method.
  • this method will not work reliably with numbers > 15 digits.

If you have numbers > 15 digits, then I suggest a VBA User Defined Function:

Option Explicit
Function digitalRoot(num As String) As Long
    Dim S As String, Sum As Long, I As Long
S = num
Do While Len(S) > 1
    Sum = 0
    For I = 1 To Len(S)
        Sum = Sum + Val(Mid(S, I, 1))
    Next I
    S = Trim(Str(Sum))
Loop

digitalRoot = CLng(S)

End Function

Answer 2

You could use a formula like:

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"))

You might need an extra SUBSTITUTE for changing . to , if that's your decimal delimiter:

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,".",",")," ","</s><s>")&"</s></t>","//s"))

However, maybe a UDF as others proposed is also a possibility for you. Though, something tells me I might have misinterpreted your question...