How to Count Unique in Google Sheets (3 Easy Ways)

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, ...])

countunique function syntax to count unique in google sheets

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:


count unique items in google sheets using countunique function

Counting from multiple ranges:


counting unique values over multiple ranges using the countunique function

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, ...])

the countuniqueifs function syntax

As you can see, the COUNTUNIQUEIFS function takes three inputs:

  1. range: The range of cells that contain the values
  2. criteria_range: The range of cells that contain our criterion
  3. 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”:


count unique values with criteria in google sheets using the countuniqueifs function

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:

  1. COUNTIF: To count the number of occurrences.
  2. UNIQUE: To return the unique values within the range.

A third function, ARRAYFORMULA, will also be utilized to present all our data at once in an array.

Our formula:


counting unique entries and their occurrences in google sheets using countif and unique function combination

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)

using query to count unique entries in google sheets

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)

counting unique entries with conditions using the query function

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.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply