Forum Discussion

ITTWS's avatar
ITTWS
Copper Contributor
Aug 23, 2023

Sum last columns with value only

Hi,   I would like to make a sum of the last 3 (variable B7) columns with a value per row. The problem is that I want to exclude the rows without the value -. In the example below green is th...
Excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Aug 23, 2023

    ITTWS As anticipated by HansVogelaar here are two possible E365 solutions.

     

    In F2 and copy down:

    =SUM(TAKE(FILTER(B2:E2,B2:E2<>"-"),,-$B$7))

    or one that spills the results in one go:

    =BYROW(B2:E5,LAMBDA(a,SUM(TAKE(FILTER(a,a<>"-"),,-B7))))

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 23, 2023

ITTWS 

=SUM(INDEX(B2:E2,LARGE(IF(B2:E2<>"-",COLUMN(A1:D1)),MIN($B$7,COUNT(B2:E2)))):INDEX(B2:E2,LARGE(IF(B2:E2<>"-",COLUMN(A1:D1)),1)))

Another alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

 

Resources