How to Count Unique Values in Google Sheets [Easy Examples]

You may use Google Sheets for many different reasons, but primarily, your work will involve values and data. You’ll often find yourself in situations where you may want to count some unique values in a large data set.

Counting them one by one or manually sounds like a terrible idea, especially when you have the luxury of doing that with much simpler Google Sheets functions.

These functions to count unique Google Sheets values are easy to use with an easy-to-understand syntax. They are basic yet essential functions to know of if you value your time, as they will help you quickly go through the process of counting unique values.

The best part about these awesome functions is that they work with text, cell references, numbers, values, etc.

In this article, I’ll overview the main Google Sheets count unique values methods and provide a step-by-step guide where I will use each method to extract different values from a sample table.

Overview of Methods to Use

There are various Google Sheets count unique values functions to easily count from a large data set. Here are four such methods that I will use to demonstrate in this article:

The =unique formula

This formula will search the cell range and return each unique item. Its syntax is:

 =unique(cell_range)

The =countunique formula

This formula will search the cell range and return the number of unique items. Its syntax is:

 =countunique(cell_range)

The remove duplicates option in Google Sheets

This formula will search the cell range and remove any duplicate items. This is not a function. Instead, it’s a data editing option provided by Google Sheets.

A combination of the Unique, Array, and COUNTIF formulas

This formula will search the cell range and return the frequency of each unique item. Its syntax is:

={Unique(cell_range),Arrayformula(Countif(cell_range,Unique(cell_range)))}

How to Count Unique Values in Google Sheets: Step-by-Step Examples

Let’s take an example case and use all four methods to generate different types of data from our example case.

So, here’s an example of 10 different people and the types of vehicles they own. I want to extract 3 different types of data from this table such as the unique vehicle types, the total number of vehicle types, and the frequency of each vehicle type.

Step 1. Create a table for unique vehicle types

There are two methods I can use to find each unique vehicle type in the lists, =unique and remove duplicates:

Using the =unique function

In the screenshot above, I’ve used the function =unique(B2:B11) to find each unique vehicle type in the list. This Google Sheet count unique function has removed all vehicle types repeated more than once and presented me with each unique vehicle type.

Using remove duplicates

For this method, first, you’ll need to bring the data from cell range B2:B11 into a new table, like so:

Then, you can use the remove duplicates option to remove any duplicate data by going to Data – Data Cleanup – Remove Duplicates.

This method provides the same results as the =unique function, but it has a few additional steps. In addition, you can also highlight duplicates with Google Sheets.

Step 2. Create a table for the number of unique vehicle types

For this step, I’ll use the Google Sheets count unique values in column function (=countunique) to determine the total number of unique vehicle types.

In this screenshot, I have used the function =countunique(B2_B11) to count the number of unique vehicle types in the list, which is 5.

Note: This count unique Google Sheets function counts the number of unique values in a column; if you wish to count the number of characters in a cell, use the LEN function.

Step 3. Create a table for the frequency of each vehicle type

Lastly, I’ll use a combination of the Unique, Array, and Countif functions to determine the frequency of each vehicle type in the list.

I’ve used the function

={UNIQUE(B2:B11),ARRAYFORMULA(COUNTIF(B2:B11,UNIQUE(B2:B11)))} 

in the above screenshot and it has displayed the frequency of each vehicle type, such as sedan = 2, suv = 3, and so on.

Conclusion

Using the Google Sheet count unique values functions elaborated above will save a massive amount of time for you.

These are definitely functions that you will make use of more often than not.

I’ve laid out every single detail about these functions that you may need. You should go ahead and give it a try to cement the concept in your mind.

I hope you can now enjoy seamless value counting on Google Sheets!

Frequently Asked Questions

How do I use Countif and unique together?

Apart from the count unique if Google Sheets function, you can use a combination of the countif and unique function like this: ={Unique(cell_range),Arrayformula(Countif(cell_range,Unique(cell_range)))}

This will help find the frequency of a value within a table. This means that this formula will search in a table and count the number of occurrences of a unique value.

How do I count unique values in Google Sheets with multiple criteria?

If you wish to conditionally count the unique values in a range with multiple criteria, the COUNTUNIQUEIFS function in Google Sheets can be used. If the conditions are met in subsequent ranges, this new function assists in counting the unique values in a range. It provides a perfect upgrade to an already useful function.

Does Countifs count unique values?

No, the countifs function alone does not count unique values, but you can use the count unique ifs Google Sheet function to count unique values within your data. Essentially, you use this Google Sheets count distinct values function to find distinct values within your data if they match your provided conditions.

Related:

Most Popular Posts

Talha Faisal

Talha Faisal

Talha is a seasoned technical writer that specializes in Automation and SaaS. Google Sheets is central to his work, and he uses his writing to make Google Sheets easy to use for everyone.