Count unique values in google sheets – in 60s (or less)

Many times you work with spreadsheets with large data sets which have repeating values. You may want to list only the unique values, find how many times they occur in a column and do further calculation with them.

You can count unique values in google sheets using the COUNTUNIQUE formula.

Here is an example animation showing this in action.

Note that the COUNTUNIQUE formula is case sensitive. Meaning if we have the first cell value “new york”, then it would count this as unique. And hence the result will be 5 instead of 4.

And here is the syntax for the COUNTUNIQUE formula.

Syntax

=COUNTUNIQUE(value1, [value2, ...])

Inputs

value1 – The first value or range to consider for uniqueness

value2, … – [ OPTIONAL ] – Additional values or ranges to consider for uniqueness.

Outputs

A number showing the count of unique values in the data set

Video

Here is a video showing count unique values in google sheets with practical examples.

Counting unique values in a dataset manually is a time consuming activity. And many times, we need to calculate unique values for further calculations or reporting.

For example, you may have an ecommerce store where you have multiple visits from each customer. While total visits is important but its equally important to know how many unique visitors browsed your website in a month on initiated checkout.

In this guide we will see such practical examples and advanced use cases of count unique values in google sheets. Lets jump in.

Table of content

• Count unique values in one column
• List unique values 
• Count occurrences of unique values
• How many unique values occur more than X times
• Count unique values in multiple column
• Count unique from 2 columns with criteria from other columns
• Remove duplicates values to show only unique values

Count unique values in google sheets in one column

A fruit shop has a list of orders for the day in a spreadsheet. They want to know how many unique items were sold on that day.

Here is the data set:-

List of Fruits

Apple

Orange

Banana

Banana

Banana

Orange

Apple

Apple

Peach

Apple

Apple

Apple

Peach

Here is the desired result they want.

total unique fruits

4

To achieve this, you can write the formula in C1 cell as 

=COUNTUNIQUE(A2:A14)

This will print the total number of unique items sold that day. Below is the final result.

As you can see, Fruit shop sold 4 unique fruits on that day. 

List unique values 

In the above example of fruit shop sales, let’s say instead of showing the count of total unique items, you want to show the individual unique items as well.

You require the final output as below:

To list unique values, you can use the UNIQUE formula 

=UNIQUE(A2:A14)

Copy and paste this formula in one cell C1 and the output will be auto printed in column C.

Here is how the result will look like:

As you can see column C lists all the 4 unique fruits sold that day.

Count occurrences of unique values

Sometimes you need to display all the unique items and how many times the item is appearing in the data set. 

Continuing to the above example, here I will show you how we can show the count of each unique fruit in the data set.

The result should look like this for the four unique fruits:

Count of uniques

6

2

3

2

To get this output you can use the below formula.

=COUNTIF(A2:A14,C2)

And here is how the result will look like:

Let’s say you do not want to depend on column C and you want to directly calculate the list of uniques and their occurrences.

Here is the formula to do that.

=ARRAYFORMULA(QUERY({UNIQUE(A2:A8) & " - " & COUNTIF(A2:A8,UNIQUE(A2:A8))},"where Col1<>' - 0'",0))

Run this formula in a new cell and see what you get.

You should get the result like this 

Apple – 6

Orange – 2

Banana – 3

Peach – 2

If you don’t then let me know in the comments.

How many unique values occur more than X times (UNIQUE + FILTER + COUNTIF)

We have counted unique items, displayed all the unique items in a column and also we have shown their occurrences. 

Let’s say you want to showcase how many times a certain item has appeared more than X times.

Suppose, you want to know how many times Apple has been sold on a particular day. 

You can use the below formula to get the desired result.

=UNIQUE(FILTER(A:A,COUNTIF(A:A,A:A)>2))

Let me explain this formula. The COUNTIF formula checks each cell and returns TRUE or FALSE if that cell item appears more than 2 times in column A. 

The FILTER formula will then filter out all such TRUE values except the FALSE ones. 

Finally the UNIQUE formula will print out the unique values among the filtered ones.

Count unique values in google sheets – Multiple column

Till now in all the above examples, we have counted unique values in a single column. When you want to count the same for multiple columns, you can use the COUNTUNIQUE() formula by selecting the range containing multiple columns.

Here is an example:

As you can see, we have 5 columns with a lot of data. To count unique values 5 columns, you can select the range from A1 to E8 and use the COUNTUNIQUE formula to get the result.

Count unique from 2 columns with criteria from other columns – (Advanced method)

Till now, you have learned how to count unique values in google sheets. 

Lets say, you want to calculate unique values from a data set based on the criteria set in other column.

For example, lets say you have an e-commerce store and you have orderers from different cities. You also have the status of these ordered whether they are delivered or Pending. 

You want to know a list of unique cities that have “Pending” status against them.

Here is the data set.

Order Id	City	Current Status
1	New york	Delivered
2	New Jersey	Pending
3	New York	Delivered
4	Austin	In Transit
5	Austin	In Transit
6	New Jersey	Pending
7	San Jose	Pending
8	San Jose	Pending

To count unique cities, we can use the below formula

=COUNTUNIQUEIFS(B2:B9,C2:C9,"Pending")

The COUNTUNIQUEIFS function takes the range where you want unique values as the first parameter. Then you put all the criteria it has to meet. You can put multiple criterias as well.

Here the criteria is to search for “Pending” status in the C column.

Here is the final result:

But lets say you want to also know which are these cities and count of “Pending” orders by city.

To do this, you need to use the query function in googl sheets.

Here is the query function to do so.

=QUERY(B2:C9, "select COUNT(B), B where C ='Pending' GROUP by B")

Let me explain this formula.

First parameter is the cell range where you want to run the query. Next is the query.

The query here says that:-

The result will output count and column B after grouping the results.

Without grouping the result will look like this

1	New Jersey
1	New Jersey
1	San Jose
1	San Jose

After grouping applied, the final result will be like this

Remove duplicates to show only unique values

Using UNIQUE function you can remove duplicates and show unique values in google sheets.

The result will show a list of unique values from the selected range.

Here is a demo showing the same in action-

FAQ

Wrapping Up

In this guide we learned how to count unique values in google sheets. We also explored how to list these unique values and perform more advanced calculations on them. 

We also learned how to count unique values in google sheets multiple columns using the same COUNTUNIQUE function. We learned the COUNTUNIQUEIFS function to add multiple criteria while searching for unique values.

With this detailed guide I am confident that you will now be able to approach any count unique problems in google sheets with ease.

Appendix

[1] COUNTUNIQUE formula in google sheets – Link

Further Reading

New to google sheets ? Start here

More related to Formulas:

Learn more about Google sheets Formulas.
Logical functions in google sheets
Error handling in google sheets