Google Sheets: Count Unique Based on Multiple Criteria

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:

Google Sheets count unique based on multiple criteria

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

Leave a Reply

Your email address will not be published. Required fields are marked *