Excel: How to Count Unique Values Based on Multiple Criteria


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

2 Replies to “Excel: How to Count Unique Values Based on Multiple Criteria”

  1. What if you wanted to count how many different conferences each person played at and scored >1. So for Bob that would be 2 and for Greg it would be 1 etc

  2. Hi, I have an issue with my situation now, i would like to do the the same exact formula but the count unique comes from different worksheet, is there a chance that the formula can be extended to also count from different worksheet? I know i can settle this issue by combining both worksheet under one worksheet but it will be more additional manual things to do and would like to reduce this. Thank you in advance!

Leave a Reply

Your email address will not be published. Required fields are marked *