excel formula - Count rows with "x" in at least one of the previous 4 cells -
i want count number of rows region (d1) , manager (f1) have @ least 1 x in previous 4 cells specified month (b1). here code have been able put adds x's in specified rows. data table in data sheet, formula , parameters (b1,d1,f1) 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))
this formula works 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")))
Comments
Post a Comment