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.

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:

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:

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.

The following tutorials explain how to perform other common tasks in Excel: