You can use the following basic formula in Excel to count unique values based on multiple criteria:
=SUM(--(LEN(UNIQUE(FILTER(A:A,(Criteria1)*(Criteria2)*(Criteria3),"")))>0))
This particular formula counts the number of unique values in column A based on three criteria being met.
The following example shows how to use this formula in practice.
Example: Count Unique Values Based on Multiple Criteria in Excel
Suppose we have the following dataset in Excel that shows the conference and points scored for various basketball players:
Now suppose we’d like to count the number of unique player names who meet the following criteria:
- The player is in the West conference.
- The player has greater than 20 points.
We can use the following formula to count the number of unique player names who meet this criteria:
=SUM(--(LEN(UNIQUE(FILTER(A2:A14,(B2:B14="West")*(C2:C14>20),"")))>0))
We can type this formula into cell E2 of our spreadsheet:
From the output we can see there are 3 unique player names that are in the West conference and have more than 20 points.
We can verify this is correct by manually identifying each player who meets both criteria:
Out of the four players who meet both criteria, there are three unique player names:
- Greg
- Sean
- Mike
Note that in this example we performed a count unique using two criteria, but we can use similar syntax to use as many criteria as we’d like.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count If Cells Contain Text
Excel: How to Count Cells Not Equal to Value
Excel: How to Use COUNTIF with OR