This guide will explain how you can use the COUNTIF formula to count duplicates in Google Sheets.
When working with data, you may be required to know how many times each unique value in a column repeats itself. We can use Google Sheets functions to determine how many duplicates are present in a given range.
Let’s take a look at a quick example where you might need to count duplicates in Google Sheets.
Suppose you are keeping track of a list of tickets your team must perform within a week. Each ticket is given a unique ID and a deadline. Given that multiple people in your team can write down which tickets they’re working on, some of your team members may be working on the same ticket.
How can we determine the number of entries in our list that are actually duplicates?
UNIQUE function, we can quickly return the number of unique values that have been submitted. Afterwards, we can use the
COUNTIF function to see how many times each unique value appears in the original list of submissions.
This use case is just one scenario where we will have to count duplicates in Google Sheets. Determining the number of duplicates in your data can help you in the data cleaning and data analysis process. When consolidating data from multiple sources, it may be unavoidable to find duplicate entries.
Now that we know when to use
COUNTIF to count duplicates in Google Sheets, let’s dive into how it works on an actual sample spreadsheet.
A Real Example of Counting Duplicates in Google Sheets
Let’s take a look at a real example of a spreadsheet in Google Sheets that can count duplicates.
We’ll be working with a simple dataset that holds multiple strings. In our table, we have 13 entries with various colors’ names.
We want to determine how many times each color has repeated entries. For example, we know that the color blue appears three times in the range A2:A1. This means that ‘Blue’ contains exactly two duplicates.
In the example below, we were able to construct another table that has a row for each unique color. Beside each color is the number of times a duplicate string was found in the given range.
To get the unique values in Column C, we just need to use the following formula:
In the example below, we’ve added an additional row to find the total number of duplicates in our original range. We’ve determined this by using the SUM function to add up all duplicate counts.
Besides our example with strings, we may use the same method to count duplicate numbers, dates, and Boolean values as well.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try counting duplicates yourself in Google Sheets, follow our guide in the next section!
How to Count Duplicates in Google Sheets
This section will guide you through each step needed to start counting duplicates in Google Sheets. You’ll first learn how to retrieve all unique values in the given range. Afterwards, you’ll use the
COUNTIF function for each of these unique values.
To get the total number of duplicates, we can add each duplicate count.
Follow these steps to start counting duplicates in Google Sheets:
- First, select the cell where we will place our unique values. In this example, we’ll place our unique values starting at cell C2.
- Next, we will use the
UNIQUEfunctions to get a sorted list of the unique values present in our range. In this example, we’ll extract the unique values from cells A2:A14.
- Now that we have our unique values, we can proceed to find the duplicates of each one. Create a new column to the right labeled ‘Duplicate Count’.
- In the new column, we’ll add a
COUNTIFformula. The first argument should reference our cell range. The second argument will contain a reference to the value we want to find duplicates of.
- Hit the Enter key to return the number of times the target value has appeared in our given range. However, we will still need to modify this formula. Notice how the text ‘Blue’ appears thrice, but we should only consider cells A5 and A11 as the actual duplicates.
- We can ignore counting the first instance by subtracting the result of our
COUNTIFfunction by 1.
- Use the Fill Handle tool to return the duplicate counts of all unique values in the range.
- Lastly, we can compute the total number of duplicates by summing up the entire ‘Duplicate Count’ column.
Frequently Asked Questions (FAQ)
- Can I count duplicates without case sensitivity?
Yes, we can ignore the case of our target values by using the
LOWERfunction to convert every character to lowercase.
The example below shows values with both uppercase and lowercase variants. When returning
UNIQUEvalues, we can first pass our data through the
This step-by-step guide should be all you need to count duplicates in Google Sheets. Our guide shows how to use the
COUNTIF values to give a detailed breakdown on how many duplicates each unique value has.
Counting duplicates is a data analysis process made easy with the Google Sheets function. With so many other Google Sheets functions available, you can surely find one that suits your use case.
Are you interested in learning more about what Google Sheets can do? Subscribe to our newsletter to find out about the latest Google Sheets guides and tutorials from us.