CountIF with multiple conditions

In the attached file, we track the number of CRs that are still open and aging but need to be closed. We have over 500 at any given time and doing this manually is becoming a tedious task. I only provided a sample... The summary tab has the table and the Data tab has the data from which I need a formula to count based on the conditions outlined... thx

It will be usefull if you add the expected result in your file (manualy).

I will make the solution with a pivot table but want to check the (expected) result.

Hi,

Your formulas might be
E8: =COUNTIFS(Data!$E:$E,$D$7,Data!$J:$J,E$7,Data!$H:$H,"<>Validated",Data!$K:$K,">="&TODAY()-30)
E9: =COUNTIFS(Data!$E:$E,$D$7,Data!$J:$J,E$7,Data!$H:$H,"<>Validated",Data!$K:$K,"<"&TODAY()-30,Data!$K:$K,">"&TODAY()-60)
E10: =COUNTIFS(Data!$E:$E,$D$7,Data!$J:$J,E$7,Data!$H:$H,"<>Validated",Data!$K:$K,"<"&TODAY()-60,Data!$K:$K,">"&TODAY()-90)
E11: =COUNTIFS(Data!$E:$E,$D$7,Data!$J:$J,E$7,Data!$H:$H,"<>Validated",Data!$K:$K,"<"&TODAY()-90)
E12: =COUNTIFS(Data!$E:$E,$D$7,Data!$J:$J,E$7,Data!$H:$H,"Validated",Data!$K:$K,"<>",Data!$L:$L,"<>")
and copy those across to the other two columns. It looks as though any date in L equates to a validated status, in which case the last formula could be shortened a little

See the attached file.

Try

in e8
=SUMPRODUCT((Data!E12:E33=$D$7)*(Data!H12:H33<>"Validated")*(Data!J12:J33=E$7)*(Data!L12:L33<>"")*(Data!K12:K33<=TODAY()+30))

in E9
=SUMPRODUCT((Data!E12:E33=$D$7)*(Data!H12:H33<>"Validated")*(Data!J12:J33=E$7)*(Data!L12:L33<>"")*(Data!K12:K33>TODAY()+30)*(Data!K12:K33<=TODAY()+60))
in E10
=SUMPRODUCT((Data!E12:E33=$D$7)*(Data!H12:H33<>"Validated")*(Data!J12:J33=E$7)*(Data!L12:L33<>"")*(Data!K12:K33>TODAY()+60)*(Data!K12:K33<=TODAY()+90))
in E11
=SUMPRODUCT((Data!E12:E33=$D$7)*(Data!H12:H33<>"Validated")*(Data!J12:J33=E$7)*(Data!L12:L33<>"")*(Data!K12:K33>TODAY()+90))
in E12
=SUMPRODUCT((Data!E12:E33=$D$7)*(Data!H12:H33="Validated")*(Data!K12:kJ33<>"")*(Data!K12:kJ33<>""))

copy across the rows