Forum Discussion

AndyB1227's avatar
AndyB1227
Copper Contributor
Jan 30, 2021

CountIf question

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.

Formulas and Functions

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 30, 2021

    AndyB1227 

    As variant on Excel with dynamic arrays

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

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 30, 2021

    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  

Resources