Count unique values in Google Sheets using the COUNTUNIQUE function.
Syntax=COUNTUNIQUE(range)range – The range of cells from which to consider unique values.
Examples=COUNTUNIQUE(B2:B10)
//Returns the number of unique items from the range B2:B10.=UNIQUE(A2:A16)//List all unique items from the range.=COUNTIF(A2:A32, B2)
//Returns the number of occurrences of an item (in B2) from the range A2:A32.
Click here to get a copy of the spreadsheet with examples and formulas.
While working with a moderately large data set, we often come across a situation where we need to count the number of unique values–names, numbers, codes, identification numbers, etc.
Luckily, there are a number of ways that can help us count unique values in Google Sheets–some simple and straightforward, others not so much. In this tutorial, we’ll learn about the various ways we can count unique values in Google Sheets as well as how we can use them in different ways.
- Count unique values in a column
- Count unique values in multiple columns
- Get all unique values
- Count the number of occurrences of unique values
Count unique values in Google Sheets using the COUNTUNIQUE function
This is a pretty straightforward function and the syntax if quite simple.
Formula for the COUNTUNIQUE function
The generic syntax for the COUNTUNIQUE function is:
=COUNTUNIQUE(range)Where range is the range of cells from which to consider unique values.
Example 1: Count unique values in a single column
Let us suppose that we have a list of customers of a small coffee shop. The following is a list of customers with their preferred coffee.
To count the number of unique coffees that customers ordered a particular day, we can use the COUNTUNIQUE function.
To do so, we simply enter the COUNTUNIQUE function with the coffees as the range. The formula is as shown below.
=COUNTUNIQUE(B2:B10)This will give us the number of unique coffees that was sold that day, as shown in the image below.
Example 2: Count unique values in multiple columns
Counting unique values in multiple columns is no different from counting unique values in a single column. The syntax is the same; we just need to select the columns that contain the data that we want to count the unique values.
We’ll consider another example using the coffee shop as our source of data.
Show underneath is a list of customers who ordered different types of coffee on a particular day. The objective this time is to find out the number of unique customers who visited our coffee shop.
To count the number of unique customers, we use the COUNTUNIQUE function. The formula is as follows:
=COUNTUNIQUE(A18:C27)This will give us the number of unique customers that visited our coffee shop that particular day, which is 19, as shown below.
Using the UNIQUE function to return all the unique values
To display the list of all the customers, we can use the UNIQUE function. The UNIQUE function looks only for different values in a column, we we need to arrange the customers in a single column. We can then use the UNIQUE function to list all unique customers in a single column.
The formula for the UNIQUE function is:
=UNIQUE(range)In our case, the range is A32:A58. Upon entering the formula in a cell, we get all the unique values in a column, as shown below.
Counting the number of occurrences of a unique value
We can count the number of times a customer ordered a coffee by using the COUNTIF function.
The formula for the COUNTIF function is:
=COUNTIF(range, criterion)To count the number of occurrences using the COUNTIF function, we’ll enter the formula below in the cell to the right of the first customer.
=COUNTIF($A$32:$A$58,B32)The dollar symbols are added to make the cell reference absolute, ie, they do not change when the formula is applied along the rows.
On pressing Enter, we get the result as follows.
Count unique values in Google Sheets using the COUNTIF and the UNIQUE functions
We can also use the ARRAYFORMULA along with the COUNTIF and the UNIQUE functions to count the number of unique occurrences, ie, how many times a value appears in a given data set. Click here to learn more about the ARRAYFORMULA.
Using the same dataset as the above, let us use the three functions to count the number of times each customer orders a coffee.
The formula will be as follows:
={UNIQUE(A32:A58),ARRAYFORMULA(COUNTIF(A32:A58,UNIQUE(A32:A58)))}Here, the UNIQUE formula returns the unique values, the COUNTIF counts the number of times unique values occurs, and the ARRAYFORMULA returns data across multiple rows and columns.
On pressing Enter, we get the result as shown below.
Conclusion
We can easily count unique values in Google Sheets using the simple built-in COUNTUNIQUE function. However, other formulas or a combination of formulas allow us to carry out more complex operations and can perform tasks other than merely counting the number of unique values, as we have seen.
I hope this article has been helpful. There a several other articles on Google Sheets on our blog.
See also
Here are some related articles that you might find useful:
https://blog.tryamigo.com/count-cells-by-color-in-google-sheets/
https://blog.tryamigo.com/how-to-count-non-empty-cells-in-google-sheets/
https://blog.tryamigo.com/exclude-a-cell-from-a-range-in-google-sheets/