Use VLOOKUP as criteria inside COUNTIFS-CodePudding

After years of using this community, I have a question that I can't find an answer to. I hope you all can help!

I am trying to get a count of the number of "Grounded" items that each individual is overdue for. My screenshot (

Formula in F9:

=SUMPRODUCT((A$2:A$7=E9)*(INDEX(H$2:H$4,MATCH(B$2:B$7,G$2:G$4,0))="Grounded")*(C$2:C$7<=F$8))

Or, with VLOOKUP() as you've planned:

=SUMPRODUCT((A$2:A$7=E9)*(VLOOKUP(B$2:B$7,G$2:H$4,2,0)="Grounded")*(C$2:C$7<=F$8))

CodePudding user response：

You could add a fourth column that stores whether that Task is Grounded

The formula for D2 would then be:

=INDEX($H$2:$H$4,MATCH(B2,$G$2:$G$4,0))

Modify your CountIfs formula to use this new column as a criteria

=COUNTIFS($A$2:$A$7,$E9,$C$2:$C$7,"<="&$F$8,$D$2:$D$7, "Grounded")