How to Count Unique Values in Google Sheets (Best Method 2023)

Are you tired of manually counting unique values in your Google Sheets? Look no further! The COUNTUNIQUE function in Google Sheets is here to save the day. Not only is it a breeze to use, but it also helps you save time when dealing with large datasets. Let’s delve into how you can utilize this powerful function, along with its cousin, COUNTUNIQUEIFS, to enhance your data analysis in Google Sheets.

Using The Google Sheets COUNTUNIQUE Function

The COUNTUNIQUE function in Google Sheets allows you to count the number of distinct values within a specified range. It’s incredibly simple to use. Just enter the formula and highlight the range where you want to apply it. The function will then count the unique values within that range. It’s as easy as pie!

Syntax

To employ the COUNTUNIQUE function effectively, use the following syntax:

=COUNTUNIQUE(range)

Within this syntax:

• Range refers to the range of cells you want to scan and count unique values.

Example

Let’s imagine you have a list of hair ties in different colors, and you want to find out how many unique colors are in your collection. Follow these steps:

1. Select the cell where you want the result to appear. For this example, let’s choose cell D2.
2. Enter the formula =COUNTUNIQUE( in cell D2.
3. Highlight the range of values you want to count. In our case, we select the entries in Column A.
4. Close the function by adding a closing parenthesis, ), and then press Enter.

And voila! You’ve successfully counted the number of unique colors in your hair tie collection.

Mr Reviews recommends using the COUNTUNIQUE function as it takes up just one cell, unlike the UNIQUE function, which occupies multiple cells in the same column. Now, let’s take it a step further and explore the COUNTUNIQUEIFS function, which allows you to filter unique values within a selected range.

Using The Google Sheets COUNTUNIQUEIFS Function

The COUNTUNIQUEIFS function in Google Sheets enables you to filter multiple columns for unique values. It’s a relatively new addition to Google Sheets and provides exceptional functionality. With COUNTUNIQUEIFS, you can compare columns using criteria like “greater than/less than,” “equal to,” and more. This function is excellent for highlighting or removing duplicate items from your spreadsheet and keeping track of completed tasks.

Syntax

To make the most of COUNTUNIQUEIFS, utilize the following syntax:

=COUNTUNIQUEIFS(countrange, criteriarange1, criterion1, [criteriarange2, criterion2, ...])

In this syntax:

• Countrange represents the range where you want to count the unique data.
• Criteriarange1 is the range where you apply criterion1.
• Criteriarange2 represents the ranges where you apply criterion2 and beyond.

Remember, at least one criterion is necessary for the function to work. COUNTUNIQUEIFS filters countrange, starting with criterion1, followed by additional criteria (if specified). It then counts the unique entries in countrange. You can use the same range for countrange, criteriarange1, and criteriarange2.

Example

To understand the COUNTUNIQUEIFS function better, let’s step into the shoes of a telemarketer. In this scenario, we want to determine how many different people answered our calls by counting the unique numbers tagged as “Answered” in our records. Follow these steps:

1. Select the cell where you want the result to appear. In this instance, let’s choose cell D2.
2. Enter the formula =COUNTUNIQUEIFS( in cell D2.
3. Set the count range and the criteria range by selecting the columns in Google Sheets. The highlighted range will automatically be entered into the formula.
   • Countrange: Range B2:B14 (where we count the unique data)
   • Criteriarange1: Range C2:C14 (where we apply criterion1)
4. Add a comma after each range.
5. Set the criterion in double quotes. In our case, set criteriarange1 as “Answered”.
6. Close the formula with a parenthesis.

Congratulations! You’ve successfully determined the number of unique values in the selected range.

Mr Reviews understands that errors can creep in when working with mathematical functions. Here are a couple of common COUNTUNIQUEIFS errors to watch out for:

Common COUNTUNIQUEIFS Errors

1. The result is 0: This occurs when none of the entries in the range matches the criterion(s) you set. If this is an incorrect result, double-check your formula to ensure you’ve chosen the right ranges for this function.
2. The formula throws a #VALUE! Error: One possible reason for this error is a discrepancy between the range of countrange and one of the criteriarange sizes. Verify that all ranges have matching sizes.

Similar Functions

You may have noticed that the first example also utilized the UNIQUE function. Alongside its aesthetic appeal in the spreadsheet, UNIQUE serves as a handy tool for cross-validation. This function lists different values in separate cells, while COUNTUNIQUE counts the number of unique values within a given range. Comparing their results can help ensure that you’ve covered the correct range using the COUNTUNIQUE function.

Conclusion

Counting unique values in Google Sheets can be immensely useful in various situations. Utilize the COUNTUNIQUE function to determine the number of unique values in a specific column. If you need to apply filters to count unique values within selected ranges, turn to COUNTUNIQUEIFS. These two powerful features of Google Sheets simplify the process of counting unique values in your data.

Experience the efficiency of Google Sheets for yourself and master the art of counting unique values today with Mr Reviews!

Mr Reviews