CODEWITHSUNDEEP
excelexcel-formula
James Owen
James Owen

Reputation: 13

Count unique items on a list that meet multiple criteria

This sounds simple, but I'm getting a real headache trying to figure it out:

I have two tables in excel in the same workbook but on different sheets. I want to count unique items in column B on the first table that meet a criteria (based on the data that's in column A of that table) and appear on the second table (on a different worksheet).

Because the data I'm working with is confidential, I've made up the two tables below (I've just clipped .jpgs). They are similarly formatted, but in reality I have much more data.

I need a formula that counts the number of unique people in Column B of Table 1 who also appear in Column B of Table 2 and whose date (in Column A of Table 1) is on or before 4/2/2016.

In this example it should come out with the answer three (for Bob, Jim, and Sue).

Table 1

Table 1

Table 2

Table 1

Any help you can provide would be hugely appreciated!!

Upvotes: 1

Views: 104

Answers (1)

Jeremy
Jeremy

Reputation: 1337

If you put this =IF(AND(COUNTIF('Second Table'!$B:$B,'First Table'!$B2)>0,$A2<DATE(2016,4,2),SUMIF(B1:$B1,B2,$C$1:$C1)=0),1,"") in C2, then auto-fill it down would do it and you could sum it then at the bottom..

Upvotes: 1

Related Questions