Sometimes when working in Google Sheets you may want to count the number of unique occurrences of data within your dataset.
This is a common task that many spreadsheet users need to learn how to do. Luckily, within Google Sheets there are a couple of ways to easily do this in your spreadsheet.
In this tutorial, I will show you how to count unique values in Google Sheets.
How to Use COUNTUNIQUE
The easiest way to count the unique values within your dataset is with the COUNTUNIQUE function.
This function counts the number of unique values in your selected range.
The syntax of this function is:
=COUNTUNIQUE(value1, [value2, …])
- value1 – this is the range of data that you want to count unique values for
- value2 – you can also add additional values or ranges to your data, this is an optional parameter
Here is how to use this formula:
- In an empty cell type the beginning of the formula =COUNTUNIQUE and press Tab on your keyboard to enter the formula
- Select your range of data that you want to count unique values for. In this example, I have selected all of column A
- Add and closing parenthesis “)” and press Enter on your keyboard
You can see in the example above I selected the entire column A in my range, and the function returned a value of 65. This means that I have 65 unique values within that selected range.
You can also use the COUNTUNIQUE function on multiple ranges, strings and text values in addition to numbers.
Here are a few examples of other ways that this formula can be used:
How to Use COUNTIF with UNIQUE
If you want to count the unique occurrences of data and count how many times each of the data appears in the dataset, using COUNTIF with the UNIQUE function is a good option.
In the previous example, we used COUNTUNIQUE, but this only returns the number of unique values in your selected range.
If we use COUNTIF with UNIQUE we can return the unique values and count the number of times each value appears in the selected range.
To do this we actually need to combine three functions: COUNTIF, ARRAYFORMULA, and UNIQUE
- COUNTIF – returns a count of the number of times data appears within a range.
- ARRAYFORMULA – allows our returned values to display across multiple columns and rows
- UNIQUE – returns the unique values and gets rid of duplicates
By combining these three formulas together, we get rid of duplicates to return unique values (UNIQUE), count the unique values (COUNTIF), and return the data across multiple rows and columns (ARRAYFORMULA)
Here is the syntax of how we will be using it. We will be using it on the same data as our last example where we have 100 rows of data in column A:
Here is how the formula will return data when in use:
You can see the formula returns the unique values in one column, and then the count of the numbers of times each unique value appears within the data range in the next column.
In this tutorial, we have shown two different methods for counting the unique values in your spreadsheet.
If you just want to count the number of unique values, COUNTUNIQUE is much faster, but if you want to also count how many times each value appears, COUNTIF + UNIQUE is the method you need.