Table of Contents
Excel has a function called COUNTIFS() which allows you to count unique values based on multiple criteria. This function takes in two or more range/criteria pairs and returns the number of cells that meet all of the criteria. For example, you can use COUNTIFS() to count the number of unique values in a range of cells based on multiple criteria, such as color, size, and shape. You can also use this function to count the number of unique values in a range of cells based on the same criteria. This function is an incredibly powerful tool for quickly getting an accurate count of unique values in a range of cells.
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.