How can I count unique data in Google Sheets using multiple criteria?

By /

To count unique data in Google Sheets using multiple criteria, you can use the COUNTIFS function. This function allows you to specify multiple criteria in different columns, and it will only count the cells that meet all of the specified criteria. This means that you can count the number of unique data points by selecting the range of cells to be counted and then specifying the criteria for each column. The function will then return the number of cells that meet all of the specified criteria, thus giving you the count of unique data based on the 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: You can find the complete documentation for the COUNTUNIQUEIFS function in Google Sheets .

Additional Resources

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

x

Related terms:

  1. Excel: How to Count Unique Values Based on Multiple Criteria
  2. Count Specific Characters in Google Sheets How to count specific characters in google sheets?
  3. How do you count unique values in Google Sheets?
  4. How to get unique values across multiple columns in Google Sheets?
  5. How do I use VLOOKUP with Multiple Criteria in Google Sheets?
  6. How to use Google Sheets: INDEX MATCH with Multiple Criteria
  7. How to Create Pivot Table with Unique Counts in Google Sheets
  8. How to return only unique rows using the Google Sheets Query function?
  9. How can I use Google Sheets’ IMPORTRANGE with multiple sheets?
  10. Google Sheets: How do I sum across multiple sheets?