Search code examples
excelexcel-formulasummultiline

Excel SUM values of a mixed Column with line-broken cells AND single-line cells

It'd be pretty obvious but I'm wondering for days: How can I SUM a column of a mix of single-line values and multi-line (Alt+Enter) values in Excel?

enter image description here

In this example, I'd like to display the Sum on the second column (7.5+12+11+1.2+2.5+3) which is 37.2. Should be easy but how can I get this in Excel?

I've tried several ways but no success so far. Thanks for any help!

Solution

  • Perhaps this would work with your version of Excel, using TEXTJOIN( ) & FILTERXML( )

    enter image description here

    • Formula used in cell C1

    =SUM(
    FILTERXML(
        "<m><b>" &
            SUBSTITUTE(
                TEXTJOIN(
                    CHAR(10),
                    ,
                    A1:A4
                ),
                CHAR(10),
                "</b><b>"
            ) & "</b></m>",
        "//b"
    )
)

    Also one needs to hit CTRL+SHIFT+ENTER while exiting the edit mode.

    Alternative approach using MS365 functions, without any LAMBDA( ) helper functions.

    enter image description here

    • Formula used in cell C1

    =LET(
     a,A1:A4,
     b,CHAR(10),
     SUM(--(TEXTSPLIT(TEXTAFTER(b&a,b,SEQUENCE(,MAX(LEN(a)-LEN(SUBSTITUTE(a,b,))+1)),,,0),b))))

    If the number of line feeds is always 2 then.

    =SUM(--TEXTSPLIT(TEXTAFTER(CHAR(10)&A1:A4,CHAR(10),{1,2},,,0),CHAR(10)))

    Another alternative approach for Excel 2019 users.

    enter image description here

    =SUMPRODUCT(IFERROR(TRIM(
  MID(SUBSTITUTE(A1:A4,CHAR(10),REPT(" ",100)),COLUMN($A$1:$Z$1)*100-99,100))+0,0))

    the above formula needs to hit CTRL+SHIFT+ENTER while exiting the edit mode.