Pull records that do not match a criteria

Hello so2605. Welcome to the forum.

Not sure I understand completely.
Try this in D2 non coverage stats and fill down.

Formula:

Please Login or Register  to view this content.

Thanks Dave! I can get the counts for the agents in the zip but I am trying to pull together a list that shows the agent name and the zip that is not in the coverage area where they ended up having a transaction. Because in the actual data there is over 100 agents I'm trying to avoid manually inputing the agent with the zip not listed in the coverage area. For example an agent has transactions in 2 zip codes that aren't listed in their coverage area. So I would need 2 rows with the agent's name and each zip in the non coverage stats sheet. I am more focused on being able to pull columns a and b instead of manually looking for each instance of a transaction in a non covered zip.

If I understand it correctly:

Take the Agent and coresponding Zip code from the 2 "sell" and "buy" list, compare with "coverage" list, then list the missing Agent and zip code in "non coverage" list?

Is it allowed using a helper column in those sheets?

Buy agent, D2, copied down:
=IF(ISNUMBER(MATCH(1,INDEX((A2='coverage zips'!$A$2:$A$11)*(B2='coverage zips'!$B$2:$B$11),0),0)),"","X")

Sell Agent D2, copied down:
=IF(ISNUMBER(MATCH(1,INDEX((A2='coverage zips'!$A$2:$A$11)*(B2='coverage zips'!$B$2:$B$11),0),0)),"","X")

Non-cov A2, copied across and down:
=IFERROR(IFERROR(INDEX('Buy agent'!A:A,AGGREGATE(15,6,ROW('Buy agent'!$D$2:$D$15)/('Buy agent'!$D$2:$D$15="X"),ROWS(A$2:A2))),INDEX('sell agent'!A:A,AGGREGATE(15,6,ROW('sell agent'!$D$2:$D$15)/(('sell agent'!$D$2:$D$15="X")*(COUNTIFS($A$1:$A1,'sell agent'!$A$2:$A$15,$B$1:$B1,'sell agent'!$B$2:$B$15)=0)),1))),"")

Non-cov C2, copied down:
=IF(A2="","",SUMPRODUCT(--(A2='Buy agent'!$A$2:$A$15),--(B2='Buy agent'!$B$2:$B$15))+SUMPRODUCT(--(A2='sell agent'!$A$2:$A$15),--(B2='sell agent'!$B$2:$B$15)))

Thanks Glenn! The formula for non coverage in cell A2 works when I update the buy agent ranges but when I update the sell agent ranges it starts returning blank cells.

If you are still looking for help, please post an .xlsx file that illustrates the issue mentioned in post #7.
Let us know if you have any questions.

Weird. I never saw your reply so2605...