CountIf question

New Contributor

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.

3 Replies

@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  

@mtarler thank you so much

it works

exactly what i needed

 

have a great weekend

 

@AndyB1227 

As variant on Excel with dynamic arrays

=SUM(INDEX(FILTER(A1:T1,A1:T1<>""),SEQUENCE(5,,COUNTA(A1:T1),-1)))