Mehrab Imtiaz
Today, we will look at a few ways we can count unique entries in Google Sheets. While the idea is simple, it also means that there can be multiple types of datasets where these methods can be applied.
Let’s begin.
3 Ways to Count Unique in Google Sheets
1. Using the COUNTUNIQUE Function of Google Sheets
The COUNTUNIQUE function is specially made to count unique values in a range in Google Sheets.
Its syntax is quite simple to understand:
COUNTUNIQUE(value1, [value2, ...])
The “value” that the function takes is simply the range of values that we will be counting unique values for. The function also allows us to add multiple ranges.
The COUNTUNIQUE function returns a single numerical value as we see in the following image:
=COUNTUNIQUE(B3:B12)
Counting from multiple ranges:
=COUNTUNIQUE(B3:B12,D3:D12,F3:F12)
COUNTUNIQUEIFS: Count Unique Values with Criteria in Google Sheets
The COUNTUNIQUEIFS function is the extension of the COUNTUNIQUE function. This has the added benefit of counting unique values according to criteria or conditions.
The syntax:
COUNTUNIQUEIFS(count_unique_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
As you can see, the COUNTUNIQUEIFS function takes three inputs:
- range: The range of cells that contain the values
- criteria_range: The range of cells that contain our criterion
- criterion: Our search condition. It can be numerical or text, depending on what you are looking for and what values are present in the criteria_range.
The COUNTUNIQUEIFS function can also extract unique occurrences from multiple sources.
As our example, we have counted all the unique names that are “present”:
=COUNTUNIQUEIFS(B3:B14,C3:C14,"Yes")
2. Counting Unique Occurrences from a List
For this section, we will utilize much of Google Sheets’ function combination capabilities. We will output all the unique values in a range and count the number of occurrences they have within it. For this, we will be utilizing two functions:
A third function, ARRAYFORMULA, will also be utilized to present all our data at once in an array.
Our formula:
={UNIQUE(B3:B12),ARRAYFORMULA(COUNTIF(B3:B12,UNIQUE(B3:B12)))}
Formula Breakdown
- Our first column,
UNIQUE(B3:B12), lists all the unique values in the range. - Our second column,
ARRAYFORMULA(COUNTIF(B3:B12,UNIQUE(B3:B12))), counts the occurrences of each of the unique values. The count corresponds to the list of items seen in the first column. - We combine the entire formula using curly braces {}.
Tip: You can make the formula more dynamic by removing the bottom limit of the cell range: B3:B12 to B3:B. This will allow any new entries to be considered in the formula as they are added to the list.
3. Using QUERY to Count Unique Values in Google Sheets
QUERY is one of the most powerful and customizable functions available to us in Google Sheets. So, it comes as no surprise that it will also be able to be customized to count unique values in Google Sheets.
We have created something similar to what we have seen in the previous section with the QUERY function:
=QUERY(B2:B12,"select B, Count(B) where B=B group by B",1)
Counting unique values with a condition in Google Sheets using the QUERY function:
=QUERY(B2:C14, "select B, count(C) where C='Yes' group by B",1)
Final Words
That concludes all the ways you can count unique values and occurrences in Google Sheets. We hope that the methods we have discussed come in handy for your spreadsheet tasks.
Feel free to leave any queries or advice you might have for us in the comments section below.
