When working in Google Sheets, you could want to count the number of unique occurrences of data within your set of data. The great news is that within Google Sheets, there are different ways to do this in the spreadsheet easily. There are two commonly used ways to count distinctive values in Google sheets.
Using COUNTUNIQUE
Table of Contents
The COUNTUNIQUE function is the easiest way to count unique values in your data set. It performs counts the number of distinct values in your designated vary. The syntax of the function is:
=COUNTUNIQUE(value1, [value2, …])
Whereby;
Value1 is the range of data you need to count unique values.
Value2 – you can opt to add additional or more values or ranges to your data; this is an optional parameter. The following method is how you apply the formula:
1. Type the beginning of the formula =COUNTUNIQUE in an empty cell and hit the Tab key on your keyboard to enter the formula
2. Make a selection of the range of data that you want to count unique values.
3. Insert a closing bracket “)” and hit Enter on your keyboard
Using the COUNTUNIQUE function on strings, multiple ranges, text values, and numbers. Here are a few examples of other ways that this formula can be used:
You can use the COUNTUNIQUE function on multiple ranges, strings, and text values additionally to numbers. The following are the different ways in which the formula is used:
Using COUNTIF with UNIQUE
If you wish to count the unique occurrences of data and the number of times individual data appears within the dataset, using COUNTIF and the UNIQUE function will help. The COUNTUNIQUE function only returns the number of unique values in the selected range. However, If you use COUNTIF in conjunction with UNIQUE, you can return the unique values and count the number of times each value appears in the selected range. To implement this, you need to combine three functions, i.e., COUNTIF, ARRAYFORMULA, and UNIQUE. Whereby;
COUNTIF – will return a count of the number of times data appears within a range.
ARRAYFORMULA -allows the returned values to show across multiple columns and rows
UNIQUE – will return the unique values and do away with duplicates
By combining the three formulas altogether, you do away with duplicates to return unique values (UNIQUE), count the unique values (COUNTIF), and return the data across multiple rows and columns (ARRAYFORMULA)
The following is the syntax of how you will apply it. You will be using an example, let’s say where you have 100 rows of data in column A:
{UNIQUE(A2:A6),ARRAYFORMULA(COUNTIF(A2:A6,UNIQUE(A2:A6)))}
The following is how the formula will return data when put into use.
You can note that the formula returns the unique values in one column and then the count of the number of times each unique value appears within the data range in the next column.
Note
If you intend to count only the number of unique values, COUNTUNIQUE is much faster, but if you also want to count how many times each value appears, COUNTIF + UNIQUE is the most suitable choice.