Search code examples
excel-formulasubtotal

Count rows with "x" in at least one of the previous 4 cells

I want to count the number of rows by Region (D1) and Manager (F1) which have at least one X in the previous 4 cells from specified Month (B1). Here is the code I have been able to put together but it only adds all the X's in the specified rows. Data table is in Data Sheet, the formula and parameters (B1,D1,F1) are in separate sheet.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Data!$A$3,ROW(INDIRECT("1:" & MATCH("zzz",Data!$A:$A)-3)),MATCH(B1,Data!$3:$3,0)-1,1,-4))*(Data!$A$4:INDEX(Data!$A:$A,MATCH("zzz",Data!$A:$A))=D1)*(Data!$A$4:INDEX(Data!$B:$B,MATCH("zzz",Data!$B:$B))=F1))

Data Table

Solution

  • This formula works for me:

    =SUMPRODUCT((Data!$A$4:$A$99=D1)*(Data!$B$4:$B$99=F1)*
 (0<COUNTIF(OFFSET(Data!$A$1,ROW($4:$99)-1,MATCH(B1,Data!$3:$3,0)-5,1,4),"x")))