CODEWITHSUNDEEP
excelexcel-formulavlookupexcel-2016
Palidor
Palidor

Reputation: 13

Use VLOOKUP as criteria inside COUNTIFS

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 (Count Tracker) shows a very condensed version of what I'm working with. Columns A-C is my large (15,000 rows) data field that is imported from an external source. In columns G-H I have listed each possible task (about 100 rows) and the type (Grounded and Non-Grounding plus many others). Column F is where I am trying to get the count.

My formula in F9 currently uses COUNTIFS to get a count of the instances in the large array that equal E9 ("Smith" in this case) and where the due date in the large array is less than or equal to the current date in F8. What I can't figure out is how to also add to count the Tasks in the large array where the task type in Column H equals "Grounded". I tried VLOOKUP, but it seems it doesn't like it and tells me there is a problem with the formula. It doesn't work, but here's the general idea of what I'm after:

=COUNTIFS($A$2:$A$7,$E9,$C$2:$C$7,"<="&$F$8,VLOOKUP($B$2:$B$7,$G$2:$H$4,2,FALSE),"=Grounded")

With how the data is currently presented, Smith should have a count of 1 and Jones should have a count of 0. I have a feeling that this is more simple than I'm making it to be, but I can't figure it out. I am using Excel 2016.

Hopefully I explained the problem well enough. Thank you for your assistance.

Upvotes: 1

Views: 484

Answers (3)

bosco_yip
bosco_yip

Reputation: 3802

Try this Sumproduct+Vlookup formula without helper solution, and can work for your Excel 2016

In F9, formula copied down :

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

enter image description here

Upvotes: 0

JvdV
JvdV

Reputation: 75840

With Excel 2016 I'd say MMULT() is a good alternative here:

enter image description here

CSE-Formula in F9:

=SUM((A$2:A$7=E9)*(MMULT((TRANSPOSE(G$2:G$4)=B$2:B$7)*(TRANSPOSE(H$2:H$4)="Grounding"),ROW(G$2:G$4)^0))*(C$2:C$7<=F$8))

Upvotes: 1

Devon Allary
Devon Allary

Reputation: 65

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")

Upvotes: 0

Related Questions