✦Template Printable Ideas Insights

How to Use COUNTIF in Google Sheets to Find Duplicates

By

Duplicates in your Google Sheets data can lead to errors, skewed analysis, and wasted time. Identifying these duplicates manually is not only tedious but also prone to human errors. This is where the COUNTIF function comes in. It's a powerful tool in Google Sheets that, with a bit of creativity, can help you find and deal with duplicates effectively. Let's dive into how to use it to overcome this critical challenge.

Before we proceed, ensure you have a basic understanding of the COUNTIF function. If not, Google's support guides offer an excellent starting point. Now, let's get started.

Using COUNTIF to Find Duplicates in Google Sheets

The main idea behind this method is to count the occurrences of each data entry. If a count greater than one occurs, then we know we have a duplicate. Let's break this down into steps.

The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
How to Use COUNTIF on Google Sheets, Infographic
How to Use COUNTIF on Google Sheets, Infographic
10 Essential Google Sheets Formulas Everyone Should Know
10 Essential Google Sheets Formulas Everyone Should Know

Preparing Your Data

Start by organizing your data in a single column for ease of use. Let's assume you want to find duplicates in Column A, titled "Data".

Next, create a new column (let's say Column B) where the results will be displayed. In cell B2, enter the following formula:

Basic Formatting in Google Sheets
Basic Formatting in Google Sheets
Remove Duplicates in Google Sheets (2 Quick and Easy Ways)
Remove Duplicates in Google Sheets (2 Quick and Easy Ways)
How to Find and Highlight Duplicates in Google Sheets [The Easiest Way]
How to Find and Highlight Duplicates in Google Sheets [The Easiest Way]
plements a column where the results will be displayed. In cell B2, enter the following formula:
/excess;', However, since Google Sheets doesn't allow such complex formulas directly, we'll use an approach that combines COUNTIF and other functions like ISBLANK, COUNTIFS, and INDEX MATCH. Let's see how this works.

Counting Occurrences - an Alternative Approach

Given the limitations of COUNTIF, let's use an indirect approach to count the occurrences of each data entry. In cell B2, enter the following ArrayFormula:

){=ARRAYFORMULA(IF(ISBLANK(A2:A),,COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))))}

This formula does the following:

Google Sheets Guide: COUNTIF Function
Google Sheets Guide: COUNTIF Function
How to find duplicates in Google Sheets
How to find duplicates in Google Sheets
How to Calculate Running Total in Google Sheets
How to Calculate Running Total in Google Sheets
How Do I Find Duplicates in Two Columns in Google Sheets?
How Do I Find Duplicates in Two Columns in Google Sheets?
Google Sheets Guide: COUNTIFS Function
Google Sheets Guide: COUNTIFS Function
the google sheets are in green and white
the google sheets are in green and white
  • ISBLANK checks if the cell in Column A is blank. If so, it skips that row.
  • COUNTIFS counts the occurrences of each data entry up to its current row.

Press Enter, and you'll see the count of each data entry in Column B.

Identifying Duplicates

Now that we have the count of each data entry, it's easy to identify duplicates. Duplicates will have a count greater than one. Here's how you can highlight these duplicates:

Using Conditional Formatting

Select Column B, then go to "Format" > "Conditional formatting" from the menu. Choose 'Format cells if...' > 'Custom formula-is'. Enter the following formula:

6 Crazy Google Sheets Formulas You Should Know
6 Crazy Google Sheets Formulas You Should Know
How to use an Excel spreadsheet in Google Sheets (access from any device, anytime) or Numbers for Ma
How to use an Excel spreadsheet in Google Sheets (access from any device, anytime) or Numbers for Ma
Master the CODE Function in Google Sheets: Easily Convert Characters to Numeric Codes! (Step-by-Step
Master the CODE Function in Google Sheets: Easily Convert Characters to Numeric Codes! (Step-by-Step
">1

Choose a format (let's say red fill) and click 'Done'. Now, any cell in Column B with a count greater than one will turn red, indicating a duplicate in the corresponding cell in Column A.

Removing Duplicates

Once you've identified duplicates, you can easily remove them. Select the data, then go to "Edit" > "Remove duplicates". Make sure to check 'Remove duplicates based on all selected columns' and click 'Remove Duplicates'.

And there you have it! You've successfully identified and removed duplicates from your Google Sheets data using the COUNTIF function and other powerful tools. Keep refining your data management skills to ensure accuracy and efficiency in your work. Happy analyzing!