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.
Table of Contents
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.
Read more: Remove Duplicates in 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:
- 5 Simple Ways to Highlight Duplicates in Google Sheets
- How to Find Absolute Value in Google Sheets [3 Ways]
- How to Copy Values and Not Formulas in Google Sheets
- How to Use the UNIQUE Function in Google Sheets: Unique Values Tutorial
- How to Find Unique Values in Google Sheets (2 Easy Methods)
- VLOOKUP in Google Sheets: The Easy Guide
- Top 11 Best Google Sheets Add-Ons
