Formula question

Question

I have a cell that contains 3 lines of numbers and I need to make sure all three numbers get included in a formula. Whenever I use the sum function, it does not include any of the numbers. How would I go about this?

Thanks

lorenzo · Answer

sandrews015&nbsp;&nbsp;With Beta function https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7*&nbsp;in&nbsp;B7:=SUM(
    BYROW(B1:B6,
        LAMBDA(rw,
            IF(ISTEXT(rw), SUM(--TEXTSPLIT(rw,CHAR(10))), rw)
        )
    )
)&nbsp;*&nbsp;can be simulated

sergeibaklan · Answer

sandrews015&nbsp;
Without modern functions
=SUM( FILTERXML("&lt;t&gt;&lt;s&gt;"&amp;SUBSTITUTE(B3,CHAR(10),"&lt;/s&gt;&lt;s&gt;")&amp;"&lt;/s&gt;&lt;/t&gt;", "//s") )

riny_van_eekelen · Answer

sandrews015&nbsp;When you say to have a cell containing three lines of numbers, that means you are&nbsp;dealing with a text string of three number separated with a line-feed (Alt-Enter). Summing texts results 0.

sandrews015 · Answer

Is there a way around this, other than giving each number its own cell?

Forum Discussion

Formula question

4 Replies

Resources