CODEWITHSUNDEEP
excelexcel-formulasum
Stefan Markovic
Stefan Markovic

Reputation: 21

Sum numbers in one cell that contains line break - Excel

I have been searching for few hours and I couldnt find any solution for this problem.

See the image

As you can see on the image I have 3 columns and 2 rows. My goal is to sum the cost row (it has line breaks) or split equipment and cost columns into 3 smaller rows. Is this possible?

Upvotes: 0

Views: 3144

Answers (2)

CallumDA
CallumDA

Reputation: 12113

Unfortunately there's not a particularly clean way to achieve this (to my knowledge). Here are a couple of things you could try though:

Method 1 - worksheet solution

If your first cost cell is in A1 then place the cursor in cell B1 and create a new named range using the following formula:

=EVALUATE(SUBSTITUTE(A1,CHAR(10),"+"))

You can then type the name of the named range into cell B1 and you'll get the sum as expected. Unfortunately you have to create a named range for this because EVALUATE isn't available as a worksheet function - only a named range function and also available in VBA.

I called my named range "eval". You can drag this formula down and it will fill down, always evaluating the cell to it's left.

enter image description here

Method 2 - VBA solution

You can use some simple VBA. Paste this into a new module and then use this formula on the worksheet like this:

=SumAlt(A1)

will return 600 in your example, if A1 contained your 100 200 300

Function SumAlt(s As String) As Long
    SumAlt = Evaluate(Replace(s, Chr(10), "+"))
End Function

Upvotes: 1

Chris Mack
Chris Mack

Reputation: 5208

Something like this in VBA will work:

Function SumLines(ByVal str As String) As Long

    Dim arr() As String

    arr = Split(str, Chr(10))

    For a = 0 To UBound(arr)
        SumLines = SumLines + CLng(arr(a))
    Next

End Function

However, this will only work if you don't have any characters other than digits and Chr(10)s (new lines).

You then use this in your worksheet, e.g.:

=SumLines(A1)

Upvotes: 1

Related Questions