Count Duplicates In Google Sheets (3 Ways)

When working with spreadsheets there will come a time when you will need to count duplicates in your data, whether it be for extraction or removal.

In this article, we will show you how to count duplicates in Google Sheets in step-by-step processes of multiple levels of expertise and situations.

3 Ways to Count Duplicates in Google Sheets

1. Using the COUNTIF Function

Let’s begin with something simple, counting duplicates in a single column using the COUNTIF function.

The syntax of the function:

COUNTIF(range, criterion)

COUNTIF syntax - count duplicates in google sheets

  • range: The selected range that will be tested against the criterion.
  • criterion: The value or condition for our test

Note: COUNTIF only works with a single criterion

We will be using the following dataset to show you how to use the COUNTIF function to count duplicates:

base table to count duplicates in google sheets

Step 1: In a cell where you want to post the count, type =COUNTIF(. For us, it is cell C3.

adding COUNTIF function to the table

Step 2: Select the range of your data and lock it in place. To lock cell range, press the F4 key of your keyboard once. Absolute signs ($) will appear before the cell references (we call this Absolute Cell Reference)

adding range to the COUNTIF function

Step 3: Input the criterion of the count. Since we want it to be dynamic, we refer to cell B3.

finalizing the COUNTIF function to count duplicates in google sheets

Step 4: Close parentheses and press ENTER.

result of COUNTIF

Step 5: Use the fill handle to cover the entire column. As we move down the rows, this formula will count every occurrence in the Items column.

applying COUNTIF to the rest of the column to count duplicates in google sheets

What we have shown so far is a pretty bare-bones method of counting duplicates or the number of occurrences of a value in a given range.

Final Formula:


Now, let’s look at some other ways in which we can make our foundation formula a bit more practical.

I. Count Duplicates and Label Unique Values

For our first upgrade, we will be labeling the unique values in our range on top of counting any duplicates that might be there. The only other function we are adding to this is the IF function.

IF Syntax:

IF(logical_expression, value_if_true, value_if_false)

It is pretty much self-explanatory.

For our example, we will be enclosing our existing formula, =COUNTIF($B$3:$B$10,B3), within the IF function and add a condition where if the count of a value is equal to 1, it will be labeled “Unique Value”.

Our new formula will be:

=IF(COUNTIF($B$3:$B$10,B3)=1,"Unique Value",COUNTIF($B$3:$B$10,B3))

adding IF condition to COUNTIF

II. Count Occurrences in a Separate List

Let’s now organize our duplicates in a separate list. We have created a separate section with all the Item names beside our main table. Here we will be counting out the number of occurrences of each item.

separate list created to count duplicates in google sheets

In the Count column, we will apply the following formula:


count duplicates in google sheets in a separate list

Our criterion is now located from cell D3 instead of the main list or table. But let us bring your attention to the range we have chosen, $B$3:B.

We have locked our starting point, $B$3, but left the end as B, which is unlocked. This will allow the criterion checks to be on the entirety of the rest of column B. As you can see in this image:

demonstration of unlocked column range

What this does is that it makes our list slightly dynamic. Meaning, if we add more duplicate items to our list, the formula will take that into account.

counting new items added to the list

We mentioned slightly more dynamic because if we add a new item to the list, it does not show in the count.

count missing new unique item added to the list

For a fully dynamic count list, see the next section.

2. Using the QUERY Function

In this section, we will be using one of the more advanced and powerful functions available to us in Google Sheets. We are talking about, of course, the QUERY function.

QUERY function syntax:

QUERY(data, query, [headers])

QUERY syntax

  • data: The range of cells where you are going to perform your query on
  • query: The query commands. They must be enclosed within quotation marks (“”). The query has its own language syntax and semantic. Find more info here: Link
  • [headers]: Optional. Indicates the number of header rows within the data range.

Continuing from our last method, we will be creating a dynamic list that counts the number of items (duplicates) within a given range.

It is a simple query formula and we apply this to any blank cell where we want our list to be. Our formula:

=QUERY(B2:B,"Select B, count (B) where B is not null group by B",1)

using QUERY to count duplicates in google sheets

Formula Breakdown

  • B2:B: Our range. It starts from cell B2 and continues indefinitely. This is our first condition to make the formula dynamic. Meaning, no matter how many items we include in this range, it will always be taken into account.
  • “Select B, count (B) where B is not null group by B”: Of course, our query is within quotation marks. Counts all the items in cells in column B that are not empty.
  • 1: We have included the header of our table in our range.

Now let’s add some new items to our Items column and see what happens:

using QUERY to dynamically count duplicates in google sheets

As you can see, the count for existing items, Desk and Pen, has increased. Adding a new item to our Items column has made our query add the item to the list (Xerox). The query has ignored the blank cell. Not only that, but the query has also listed all the items in alphabetical order.

Known Disadvantages (Perhaps?):

The query is case-sensitive.

3. Alternatives (Situational)

If the query method proves a bit complicated for you, we have some other alternatives that will give you similar results.


The first formula we have on our list is a combination of ARRAYFORMULA, UNIQUE and COUNTIF functions:


using ARRAYFORMULA and and UNIQUE to count duplicates in google sheets

Formula Breakdown:

  • The first UNIQUE application returns the unique values in the range B3:B.
  • The second UNIQUE application is the criterion for the COUNTIF function, which checks for unique values within the range.
  • The ArrayFormula displays the values from the array formula/function into the rows and columns automatically.


The next formula is the closest that represents the QUERY formula, the SORTN function:

=SORTN({B3:B, COUNTIF(B3:B, B3:B)}, 9^9,2,1,1)

Direct but complex alternative to QUERY

This formula also takes an array value, but it also sorts all the values in alphabetical (ascending) order.

Data Cleanup

You may have noticed an extra 0 after every count. To remove that, simply replace the cell range, B3:B in our case, with this formula:

FILTER(B3:B, B3:B<>"")

Cleaning up data for count

This will help ignore blanks in the range. Also, be careful about any parentheses. We don’t want to be hit with a parse error, do we?

Remove Duplicates

Before our sendoff, a small tip from us: How to remove duplicate values from a selected range. The need might arise when you need to remove the duplicate values we have learned so far to count.

Step 1: Select the range of data that contains duplicates.

Step 2: Navigate to the Data tab > Data cleanup > Remove duplicates

navigating to remove duplicates

duplicates removed pop-up dialog box

Final Words

In this article, we have tried to show you how to count duplicates in Google Sheets at every level of complexity. We hope that the issue you are facing can ve solved by any of the methods mentioned here, or if you just want to try something new for your spreadsheet.

Feel free to comment with any questions or advice you may have for us!

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