In this article, we will see how to count unique values in google sheets, oftentimes we need this function in google sheets to read and display unique values.
We may have a big data set and there are high chances of duplications and redundancies. To achieve high-quality data, we perform normalization by any means. In this scope we have unique, a method/function to count unique values in google sheets. Today we will see about the unique method, variations, and use cases of how to count unique values in google sheets.
We will discuss each use case separately in different sections. So, let’s get started.
Use case of Count Unique Values in Google Sheets
As I mentioned, most of the time we have such a huge dataset, and there are very high chances of duplication and redundancies. For a professional presentation, we want to remove duplications and present unique data, for this purpose we need a method/function/formula to extract unique values out of the entire spreadsheet. We also need to count unique values in google sheets to calculate the actual size of the data that is unique. This may help in data presentation, data manipulation, data optimization, and much more.
How to Count Unique Values in Google Sheets
Imagine or create a scenario where you have large data set and now you need to present it in a meeting. The priority is always to be precise and clear with your data. Data clarity and effectiveness fully depend on its presentation. If the data contains duplications, you will lose the first impression. We will solve this problem by learning how to count values in google sheets in the step-by-step procedure with screenshots.
We have multiple methods to count unique values in google sheets we will see all methods and learn them with a step-by-step procedure.
Using COUNTUNIQUE Formula to Count the Unique Values
CountUnique is a built-in formula in google sheets that is used to count unique values in google sheets within a specified range of data.
To understand it, please see the step-by-step procedure below.
Identify the range of data from which you want to get the unique values only
Select a nearing cell, and start writing the formula
Understanding COUNTUNIQUE formula
- In the above formula, we have =countunique: It is the formula name
- then we have the opening brace (
- then we have a cell address, this cell address will be the starting point of the range
- then a colon sign (for data range only)
- then we have another cell address, this cell address will be the ending point of the range
- then closing brace
Hit enter, and you’re done, you will get the count of the unique values in the selected range.
This is it; you saw how simple it is to count unique values in a range.
Using UNIQUE Formula to Display the Unique Values
In this section, we will see how to display unique values in google sheets, in the last section we were showing the count of the unique values, but in this particular section, we will see how we can display each unique value of a range using a unique formula. Let’s see how it is done in a few steps
It’s simple, we only need to count from the above formula and now it will display instead of count only.
Identify the range from which you want to display unique values only.
Start writing the formula
This is the same formula as we used in the previous section, the only difference is the count keyword which is eliminated to display the unique values.
Hit Enter and unique values will be displayed
This is how you can use this simple formula to display only the unique values from a range.
Using Remove Duplicates to Display the Unique Values
Here in this section, we will see another method in google sheets to display unique values, we will be using remove to duplicated to display the unique values. It is doing the same work as we have done using unique. But there is a critical difference you need to know.
The remove duplicated method will be mutable and it changes the original data unlike unique, unique gives us the unique data in a separate column, but removing duplicated removes the duplicate entries within the original column. Now it is up to the user which methods he wants to use according to the work requirement.
It is very simple to use remove duplicates in google sheets, simply follow the step-by-step procedure below.
Select your data range/column from which you want to remove duplicates.
Go to Data > Data Clean-up > Remove duplicates
A Remove duplicates pop-up will appear, check on “data has header row” if your range contains the header row.
In the next section, “columns to analyze” check on both checkboxes “Select All” and “Column A”.
Click on Remove Duplicates
A confirmation pop-up will appear, click Ok
You’re done. Now see your data has unique values left behind.
This is how you can easily normalize your data and remove redundancies in the original column.
Using UNIQUE and COUNTIF to Display the Occurrences of Each Unique Value
In this section, we will be using unique and countif to display the occurrences of each unique value in a range. We will learn how to compile these two formulas to find out the occurrences of each value in a specified range.
We need this feature when we are working on data validation or data testing and we want to analyze the number of unique entries out of many, so we use this method to easily find out the occurrence of each unique value in a range. Let’s see how it works.
Identify the range from which you want to display the occurrence of each unique value
In any cell, start writing the formula
The above formula is a combination of two formulas inside a curly brace, the outer formula is a unique formula and the inner formula is again a combination of countif and unique inside an array formula because they are making an array.
Firstly, The outer unique formula will find out the unique values of the range
then the inner count will count the values of the range, and the unique will display them.
This is how we do it using unique and countif to display the occurrences of each unique values
How to Count Unique Values in One Column in Google Sheets
In this section we will learn how to count unique values in one section in google sheets, It is very simple and we do it using a count unique formula.
Pick a cell to get the count value
Start writing the formula
Hit enter and you’re done.
How to Count Unique Values in Multiple Columns in Google Sheets
So, are wondering how we will count unique values in multiple columns? In this section, we will learn how to count unique values in multiple columns
For doing this simply follow the below steps.
Pick a cell where you want to get the count
Start writing the formula
Hit Enter, and you’re done. You have got the unique values to count from multiple columns.
We can verify the number of unique visually by counting the unique entries. Here we can see 7 unique city names available which are marked with red underline. The number we got is 7 which verifies the result.
Tutorial: How to Count Unique Values in Google Sheets
- Always use unique to count the number of unique values
- Always check on data has a header row checkbox when your selected range contains the header row in it.
- Use countif along with unique to display the unique value and their occurrences.
- You can also use countifs similar to countif with the combination of unique
Some FAQs to Organize Your Work
What is simple count in google sheets?
The count function is a function in google sheets to count the cells in a specified range of data. But the count function does not count any data type other than the number String and dates.
What if we need to count other data types too?
For counting all data types, we have another function which is CountA in google sheets. CountA function counts everything including #div!, special characters, symbols, hyperlinks, etc. It doesn’t count blacks or empty cells
What function do we use for counting some special cells only?
Similar to count and countA we have countif in google sheets, count if is used to count a data range based on a condition, it can be a “string”, or a “number” that needs to be in the cell to be counted.
What if I have multiple conditions to check? can I still use countif?
To check more than 1 condition, we can’t use countif simply. We have a formula that is countifs, the plural of countif.
How can we count blanks? Do both count and countA skip blank cell?
Yes, count and counta skip blank cells, for counting blank cells we can use a very simple formula that is countblank. It counts the occurrence of blank cells within a range.
We learned many variations of count in google sheets. To understand the count function, you need to practice it and use it for different use cases. Practice all the variations of count we learned in this article, and see how can we use more combinations as we used in Using UNIQUE and COUNTIF to display the occurrences of each unique value.
I hope you like the article, if you do then share it with your buddies. Enjoy the learning and don’t forget to subscribe to the Office Demy blog for more exciting updates.