Excel: Sum all numbers separated by new line within cell

Question

How do I create a new cell that equals the sum all numbers in a cell separated by a new line?

I may not use text to columns as that is messy and there's no new line separator delimiter in text to columns in Excel and that's extra overhead.

Answer 1

In a comment, you asked "does this work on an apple".

Mac computers do not have the FILTERXML function.

However, we can substitute:

=SUM(IFERROR(--MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",99)),IF(seq=1,1,(seq-1)*99),99),0))

or, if you have the NUMBERVALUE function:

=SUM(NUMBERVALUE(MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",99)),IF(seq=1,1,(seq-1)*99),99)))

where seq is a defined NAME referring to:

=ROW(INDEX(Sheet16!$A:$A,1):INDEX(Sheet16!$A:$A,255))

In the above we

• Replace each lf with 99 spaces
• Use the MID function to return an array of the values in the cell
• Convert those values to their numeric equivalent with either the NUMBERVALUE function; or using a double-unary.
  • Since the double-unary will => error when applied to an empty string, we need to test for that with IFERROR.
  • That is not an issue if we use NUMBERVALUE which returns 0 when applied to an empty string.
• SUM the values in the resultant array

Answer 2

I think the only way to correctly calculate with these values, is to first merge 'm to cells with Text to columns. I found a nice way to do this

• Select your cell and choose [Data] > Text to Columns
• Choose for "Delimited" and hit next
• Select "other" and in the empty textbox, press CTRL + J (nothing will appear)
• Hit finish and your data will be spread horizontally over the cells.

Now you can just do a sum like =SUM(2:2)

Answer 3

The macro below will get the sum you want.
Just select the cell below the one you want to get sum of. Then call this macro. It will write the sum in selected cell. See the sample below:

Sub getsum_fromcell()
Dim newarr As Variant
mycell = Cells(ActiveCell.Row - 1, ActiveCell.Column)

newarr = Split(mycell, Chr(10))
                    mysum = 0
                    ubnd = UBound(newarr)
                    For i = 0 To ubnd
                        mysum = mysum + newarr(i)
                    Next i
ActiveCell.Value = mysum
End Sub

I am also pasting below the function version, in which you can get sum of any cell you will give to the function.

Function getsum_fromcell_func(mycell)
Dim newarr As Variant
newarr = Split(mycell, Chr(10))
                    mysum = 0
                    ubnd = UBound(newarr)
                    For i = 0 To ubnd
                        mysum = mysum + newarr(i)
                    Next i
getsum_fromcell_func = mysum
End Function

Answer 4

If you are using Excel for Microsoft 365, Excel 2019, Excel 2016, or Excel 2013:

You can perform this formulaically with the very useful FILTERXML().

=SUM(FILTERXML("<a><z>"&SUBSTITUTE(A1,"
","</z><z>")&"</z></a>","//z"))

Note that this is two lines long because of the literal line break in there. You could replace that with CHAR(10) (the ASCII equivalent) to make it one line.

=SUM(FILTERXML("<a><z>"&SUBSTITUTE(A1,CHAR(10),"</z><z>")&"</z></a>","//z"))

I won't pretend to understand everything about FILTERXML() because I've only ever used it to turn a string into an array. However, that's exactly what you need. My understanding is that it interprets text as XML data and then tries to display that data the way it would look in a browser or other software meant to display XML data. By wrapping your text in XML tags and replacing the line breaks with tags, you can make it look like XML data which Excel happily displays as several individual cells instead. You can wrap all that in a SUM() function since that's the result you want.

Answer 5

Indeed you can achieve this within one cell and without any macros.

Assuming your source data is in cell A2 like in your screenshot:

=SUM(FILTERXML("<x><y>" & SUBSTITUTE(A2, CHAR(10), "</y><y>") & "</y></x>", "//y"))

This is an array formula, so you have to finish entering it with ctrl + shift + enter for it to work properly.

How does this formula work? With a little trick:

1. First it replaces all linebreaks in your data with the string </y><y>.
2. It encloses the new string with <x><y> and </x></y>, creating a valid xml table.
3. FILTERXML converts the xml table into a data array.
4. Finally it sums up all numbers from the array.

With this you should be good. Your data can even contain text lines, the formula will simply ignore all text.

EDIT: According to the documentation, the function FILTERXML is currently not available in Excel for the web and Excel for Mac.