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

May 8, 2024

The COUNTUNIQUE function in Google Sheets can be used to count the number of unique values in a dataset. This function is easy to use and can save time when working with large datasets. It can be applied to a range of cells and will return the number of distinct values within that range.

If you needed to tally different values in a selected range while filtering it at the same time, you could use COUNTUNIQUEIFS, which combines COUNTUNIQUE with IFS.

These functions are unique to Google Sheets, and will make your work easier.

## Using The Google Sheets COUNTUNIQUE Function

This is a very easy function to use in Google Sheets. Enter the formula then highlight the range where you want to use it. It will count the unique values in the selected range!

### Syntax

=COUNTUNIQUE(range)

Where

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

### Example

Let’s say you have a list of hair ties and their colors, and you want to find out how many colors were in the basket.

#### Step 1: Select the Cell Where You Want to Place the Result

Select the cell where you want the result to appear. For this example we want to place this at cell D2 so we select cell D2

#### Step 3: Select The Range Where You Want to Count

Highlight the range of values that you want to count. It will be automatically entered into the function. For our example we highlighted the entries at Column A.

#### Step 4: Close the Function With Closing Parenthesis

Close the function with the closing parenthesis “)” and then press Enter.

That’s it!

This function only takes up one cell while UNIQUE takes up multiple cells in the same column in Google Sheets.

Next, we will take things a step further by using the COUNTUNIQUEIFS function to apply criteria to filter the unique values in the selected range.

## Using The Google Sheets COUNTUNIQUEIFS Function

COUNTUNIQUEIFS is a way of filtering multiple columns for unique values. It is a relatively new function in Google Sheets. You can compare columns with “greater than/less than,” “equal to,” and anything else you could do with the IFS function

This is useful if you need to highlight or remove duplicate items in your spreadsheet. It is also useful to track what you have and have not done already.

Finally, it’s important to know that COUNTUNIQUEIFS is also only available in Google Sheets. You can’t do this in Excel!

### Syntax

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

Where

Countrange is the range where we count the unique data

Criteriarange1 is the range where we apply criterion1

Criteriarange2 is the ranges where we apply criterion2

And so on.

At least one criterion is needed for the function to work.

This function filters countrange first by applying criterion1 first (to be followed by succeeding criteria if specified) and then counting the unique entries in the countrange. You can specify the same range for countrange, criteriarange1 and criteriarange2.

The criteria you can use can be one of the following:

• Equal to a specific value
• Greater than or greater than or equal
• Less than or less than or equal

### Example

Telemarketers are the bane of many people’s existences, whether they are human or not. But you might not know that both human telemarketers and robots will pay attention if someone answers the phone. And if someone consistently answers the phone at a specific time of day, the telemarketer will know that that is a good time to reach them.

We are going to get into the head of a telemarketer. For this example, we want to know how many different people answered our calls.  We will count the unique numbers that are tagged as “Answered” in our records.

#### Step 1: Select the Cell Where You Want to Place the Result

Select the cell where you want the result to appear. In this case we want to place the result in cell D2 so we select cell D2.

#### Step 3: Set the Count Range and the Criteria Range

Select the columns in Google Sheets where you want to find the different values. Once you highlight them, they will be entered into the formula automatically.

Select the columns in the following order:

• Countrange: the range where we count the unique data. For our example it’s B2:B14.
• Criteriarange1: the range where we apply criterion1. For our example it’s C2:C14.

Add a comma after each range.

Make sure the columns you want to compare with COUNTUNIQUEIFS have the same amount of data in them; if they do not match, you will get an error.

#### Step 4: Set the Criterion in Double Quotes

As we want to count how many phone numbers answered our call, we will set “Answered” as the criteriarange1.

The formula should look like:

#### Step 5: Close the Function with the Closing Parenthesis

Close the function with the closing parenthesis “)” and then press Enter.

### Common COUNTUNIQUEIFS Errors

#### The result is 0

None of the entries in the range matches with the criterion/criteria you set. If this is a wrong result, you should check the formula again, making sure you set the right ranges for this function.

#### The formula throws a #VALUE! Error

One reason is that the range of the countrange and one of the criteriarange has different sizes. Check them all to make sure their range sizes match.

## Similar Functions

You may have noticed that the first example also used the UNIQUE function for comparison’s sake. Along with looking nice on the spreadsheet, UNIQUE is also a good way of checking your work. This function lists down the different values in their own separate cells while the other counts how many different unique values there are in the given range. Their results should match and can be used to check if you covered the right range using the COUNTUNIQUE function.

## Conclusion

There are many situations where you may want to count unique values in Google Sheets.

Use COUNTUNIQUE function to find out how many unique values you have in a given column.

If you want to apply filters to count the unique values in the selected ranges, use COUNTUNIQUEIFS

These two features of Google Sheets will make it easy to count the unique values in your data!

If you enjoyed this article, you might also like our article on how to count words in Google Sheets or our article on how to count rows in Google Sheets.

If you want to learn how to set reminders in Google Sheets, we also suggest checking out our detailed guide.

Use our renewal tracker to easily set up custom reminders from your spreadsheet in just a few clicks.

Get Google Sheets productivity and automation tips delivered straight to your inbox