You can use the following basic syntax in Google Sheets to count unique values based on multiple criteria:
=COUNTUNIQUEIFS(A2:A11, B2:B11, "West", C2:C11, ">20")
This particular formula counts the number of unique values in the range A2:A11 based on both of the following two criteria being met:
- The corresponding value in the range B2:B11 is “West”
- The corresponding value in the range C2:C11 is greater than 20
The following example shows how to use this formula in practice.
Example: Count Unique Values Based on Multiple Criteria in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the conference and points scored for various basketball players:
Suppose that we would like to count the number of unique players 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 players who meet this criteria:
=COUNTUNIQUEIFS(A2:A11, B2:B11, "West", C2:C11, ">20")
We can type this formula into cell E2 of our spreadsheet:
From the output we can see there are 3 unique players 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:
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.
Note: You can find the complete documentation for the COUNTUNIQUEIFS function in Google Sheets here.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: Create Pivot Table with Unique Counts
Google Sheets: How to Use VLOOKUP with COUNTIF
Google Sheets: Remove Duplicate Rows Based on One Column
