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:

• 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:

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

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)

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

Step 4: Close parentheses and press ENTER.

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.

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:

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:

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.

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

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:

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.

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

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:

• 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:

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:

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.

I. Using ARRAYFORMULA

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

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.

II. Using SORTN

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

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:

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

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

• Highlight Duplicates in Google Sheets (4 Ways)
• COUNTIF Multiple Criteria in Google Sheets (3 Ways)
• COUNTIF From Another Sheet in Google Sheets
• COUNTIF Across Multiple Sheets in Google Sheets