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.