You can use the following methods to count unique values based on criteria in Google Sheets:
Method 1: Count Unique Values Based on One Criteria
=COUNTUNIQUEIFS(A2:A10, C2:C10, ">30")
This particular formula counts the number of unique values in the range A2:A10 where the corresponding value in the range C2:C10 is greater than 30.
Method 2: Count Unique Values Based on Multiple Criteria
=COUNTUNIQUEIFS(A2:A10, B2:B10, "Forward", C2:C10, "")
This particular formula counts the number of unique values in the range A2:A10 where the corresponding value in the range B2:B10 is equal to “Forward” and the value in the range C2:C10 is less than 20.
The following examples shows how to use each method in practice with the following dataset in Google Sheets:
Example 1: Count Unique Values Based on One Criteria
We can use the following formula to count the unique values in the Team column only where the corresponding values in the Points column is greater than 30:
=COUNTUNIQUEIFS(A2:A10, C2:C10, ">30")
The following screenshot shows how to use this formula in practice:
The output tells us that there are only 2 unique values in the Team column where the corresponding value in the Points column is greater than 30.
If we look at the original dataset we can see that there are 3 rows where the value in the Points column is greater than 30, but there are only two unique team names among those 3 rows: Mavs and Spurs.
This is why the COUNTUNIQUEIFS formula returns a value of 2.
Example 2: Count Unique Values Based on Multiple Criteria
We can use the following formula to count the unique values in the Team column only where the corresponding values in the Position column is “Forward” and the value in the Points column is less than 20:
=COUNTUNIQUEIFS(A2:A10, B2:B10, "Forward", C2:C10, "")
The following screenshot shows how to use this formula in practice:
The output tells us that there are only 2 unique values in the Team column where the corresponding value in the Position column is “Forward” and the value in the Points column is less than 20.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
Google Sheets: Create Pivot Table with Unique Counts
Google Sheets: Create Unique List Across Multiple Columns
Google Sheets Query: How to Return Only Unique Rows