How to Count Unique Values in Google Sheets (2 Easy Methods)

Welcome to Google Spreadsheet Tips and Tutorials Hub! In this tutorial, I am going to discuss how to count unique values in Google Sheets.

Since counting unique values in your data can be something which you might need to do more often than not; this article is here to show you exactly how you can do just that easily and be able to avoid duplicate values.

Needing to count cells unique values in your Google spreadsheet can become a regular task if you’ve been working on your spreadsheet quite a lot. Hopefully, by the end of my article, you’ll be able to do exactly that so, lets’ get started!

Method #1: How to Count Unique Values Using the COUNTUNIQUE Function in Google Sheets

I think this is simplest way to count the unique values in your data. All you have to do is, type in the following formula in a selected cell:

=COUNTUNIQUE (value_1, value_2)

Let me explain the syntax of this COUNTUNIQUE function formula.

The first augment is the function for counting unique values.

The second augments are the range of cells within which you want to execute your search for unique values.

How to use this formula? If I were to search for any unique values, I would first,

  • Select a cell and type in my formula.
How to Count Unique Values in Google Sheets Using the COUNTUNIQUE Function
  • Select my cell range. In this case, my cell range would be column A, specifically cell A2 to cell A7. The second range of values in the formula is for any extra information; however, this criteria is totally an option. So the formula is:
=COUNTUNIQUE(A2:A7)
While using COUNTUNIQUE Function select the range in the formula in order to get unique values from
  • Press the “Enter” key to get the results.
Results of COUNTUNIQUE Function and got the Unique values

You can apply the COUNTUNIQUE function on all the following kinds of data too:

  1. More than one range of values
  2. Multiple ranges
  3. Multiple text values

Method #2: Count Unique Values Using the Combination of COUNTIF and UNIQUE Functions

In the previous formula I showed above, you can only get the number of unique values. However, with this method of using the COUNTIF function with the UNIQUE function and, with the help of another function I will show you – how you can see both the number of unique values, plus the frequency of occurrences of those unique values.

So this is how it will go:

First off, these are the functions which you need to use, in order to apply this method of counting unique values in Google Sheets:

  1. The COUNTIF function
  2. The UNIQUE function
  3.  The ARRAYFORMULA function

The COUNTIF function gives you the frequency of a unique data’s appearance within a cell range. Moreover, the UNIQUE function is responsible of finding out the unique data and filtering out repeated data. Lastly, the ARRAYFORMULA function returns the values to display over a number of rows and columns.

And voila! You have your unique values shown to you (UNIQUE) across more than one rows and columns (ARRAYFORMULA), also, you can see the number of times those unique values have occurred in your data (COUNTIF).

This is the syntax of the formula:

={UNIQUE(cell range),ARRAYFORMULA(COUNTIF(cell range, UNIQUE(cell range)))}

The first augment of this formula deals with finding the unique values from my data.

Then, the second of it is responsible for displaying the values over a number of rows and columns.

And lastly, the third augment of the syntax of this formula is the COUNTIF and, this COUNTIF function has to count all of the unique values (that’s why there is the UNIQUE function as a sub-function of th4e COUNTIF function).

For my example google data sheet the formula will be:

= {UNIQUE(A2:A7),ARRAYFORMULA(COUNTIF(A2:A7, UNIQUE(A2:A7)))}
How to Count Unique Values in Google Sheets

As you can see in the above example, this is how you can use the formula to get the results.

How to Count Unique Values in Google Sheets Using the Combination of COUNTIF and UNIQUE Functions

The figures in column C indicates the number of times each unique value has appeared. For instance, the value “20” has occurred two times.

To Conclude

And that’s how you can easily count unique values in Google Sheets. I hope my tutorial will be of use to you n the future. Best of luck!

Few More Related Google Sheets Tutorials on:

Google Sheets Count Cells with Specific Text

How to Count Non-Empty Cells in Google Sheets (Count Cells If Not Blank)

Google Spreadsheets Script: Beginner’s Guide

How to Use the Quartile Function in Google Sheets

How to Speed up Google Sheets (10 Easy Ways)

Was this article helpful?
YesNo


Related More Articles

Leave a Comment