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 t...
Formulas and Functions
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  

  • AndyB1227's avatar
    AndyB1227
    Copper Contributor
    Jan 30, 2021

    mtarler thank you so much

    it works

    exactly what i needed

     

    have a great weekend

     

Resources