How to Count Unique Values in Google Sheets (4 Methods)

How to count unique values in google sheets 3

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.

See also  How to Use SUMIF in Google Sheets [User Guide]

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.

1

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.

Step 1

Identify the range of data from which you want to get the unique values only

How to count unique values in google sheets 1

Step 2

Select a nearing cell, and start writing the formula

=COUNTUNIQUE(A1:A19)

How to count unique values in google sheets 2

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

Step 3

Hit enter, and you’re done, you will get the count of the unique values in the selected range.

How to count unique values in google sheets 3

This is it; you saw how simple it is to count unique values in a range.

See also  How to Apply Conditional Formatting to Find Duplicates in Google Sheets
2

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.

Step 1

Identify the range from which you want to display unique values only.

How to count unique values in google sheets 4

Step 2

Start writing the formula

=UNIQUE(A1:A19)

How to count unique values in google sheets 5

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.

Step 3

Hit Enter and unique values will be displayed

How to count unique values in google sheets 6

This is how you can use this simple formula to display only the unique values from a range.

See also  How to Add Equation to Graph in Google Sheets
3

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.

Step 1

Select your data range/column from which you want to remove duplicates.

How to count unique values in google sheets 7

Step 2

Go to Data > Data Clean-up > Remove duplicates

How to count unique values in google sheets 8

Step 3

A Remove duplicates pop-up will appear, check on “data has header row” if your range contains the header row.

How to count unique values in google sheets 9

Step 4

In the next section, “columns to analyze” check on both checkboxes “Select All” and “Column A”.

How to count unique values in google sheets 10

Step 5

Click on Remove Duplicates

Step 6

A confirmation pop-up will appear, click Ok

How to count unique values in google sheets 11

You’re done. Now see your data has unique values left behind.

How to count unique values in google sheets 12

This is how you can easily normalize your data and remove redundancies in the original column.

See also  How to Format Cells in Google Sheets [Guide 2023]
4

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.

Step 1

Identify the range from which you want to display the occurrence of each unique value

How to count unique values in google sheets 13

Step 2

In any cell, start writing the formula

={UNIQUE(A1:A10), ARRAYFORMULA(COUNTIF(A1:A10,UNIQUE(A1:A10)))}

How to count unique values in google sheets 14

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.

Formula Explanation:

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.

Step 3

Hit Enter

How to count unique values in google sheets 15

This is how we do it using unique and countif to display the occurrences of each unique values

See also  How to Insert Radio Button in Google Sheets (Best Practice)

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.

Step 1

Pick a cell to get the count value

How to count unique values in google sheets 16

Step 2

Start writing the formula

=COUNTUNIQUE(A1:A10)

How to count unique values in google sheets 17

Step 3

Hit enter and you’re done.

How to count unique values in google sheets 18

See also  How to Add Calculated Fields in Google Sheets (Ultimate Guide)

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.

Step 1

Pick a cell where you want to get the count

How to count unique values in google sheets 19

Step 2

Start writing the formula

=COUNTUNIQUE(A1:A10)

How to count unique values in google sheets 20

Step 3

Hit Enter, and you’re done. You have got the unique values to count from multiple columns.

How to count unique values in google sheets 21

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.

How to count unique values in google sheets 22

To recap, we learned how to count unique values in google sheets, we learned how the simple count function works. We also saw how can we count unique values in google sheets. We have seen many variations of count and unique, we discussed and implement the step-by-step procedure to count unique values in one column, and in multiple columns, we saw how can we display the unique values within a data range and, how can we count the occurrence of each unique value within a data range. In the FAQs section, we discussed some commonly used variations of the count function in google sheets.
See also  How to Search in Google Sheets (Complete Guide)

Tutorial: How to Count Unique Values in Google Sheets

How to count unique values in google sheets Animation

Notes

  • 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.

See also  How to Make a Box Plot in Google Sheets (Best Practice)

Conclusion

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general