Excel: Sum all numbers separated by new line within cell

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.

Solution 1:

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

Solution 2:

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.

Solution 3:

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.