Forum Discussion

sandrews015's avatar
sandrews015
Copper Contributor
Jun 13, 2022

Formula 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 ...
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jun 13, 2022

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

sandrews015's avatar
sandrews015
Copper Contributor
Jun 13, 2022
Is there a way around this, other than giving each number its own cell?
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 13, 2022

    sandrews015 

     

    With Beta function https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7*

     

    in B7:

    =SUM(
    BYROW(B1:B6,
        LAMBDA(rw,
            IF(ISTEXT(rw), SUM(--TEXTSPLIT(rw,CHAR(10))), rw)
        )
    )
)

     

    * can be simulated

Resources