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.
Read on to learn more about how to use each of these functions in Google Sheets.
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!
=COUNTUNIQUE(range)
Where
Range is the range of cells you want to scan and count unique values.
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.
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
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.
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.
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!
=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:
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.
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.
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:
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.
As we want to count how many phone numbers answered our call, we will set “Answered” as the criteriarange1.
Add “Answered” in quotes after your values and close the formula with a parenthesis.
The formula should look like:
=COUNTUNIQUEIFS(B2:B14, C2:C14, “Answered”)
Close the function with the closing parenthesis “)” and then press Enter.
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.
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.
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.
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.
