Jan 29 2021 05:01 PM
I have about 20 columns that either have a value between 0 and 25, or might have no value. I want an additional column that can total the 5 previous cells in that row that have a value, but ignore the cells with no value.
Jan 29 2021 05:24 PM
@AndyB1227 try this:
=SUMPRODUCT((A1:T1)*(LARGE(ISNUMBER(A1:T1)*COLUMN(A1:T1),5)<=COLUMN(A1:T1)))
where A1:T1 is the range of 20 cells and the "5" just before the "<=" is the max number of numeric cells to sum
Jan 29 2021 06:18 PM
@mtarler thank you so much
it works
exactly what i needed
have a great weekend
Jan 30 2021 01:16 AM
@AndyB1227
As variant on Excel with dynamic arrays
=SUM(INDEX(FILTER(A1:T1,A1:T1<>""),SEQUENCE(5,,COUNTA(A1:T1),-1)))
