How to Count Duplicates in Google Sheets

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?

With the 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.

list of values which we need to search for duplicates

 

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.

new table with unique values and number of duplicates

 

To get the unique values in Column C, we just need to use the following formula:

=SORT(UNIQUE(A2:A14))

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.

adding all duplicates to get total

 

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:

  1. First, select the cell where we will place our unique values. In this example, we’ll place our unique values starting at cell C2.
    select cell to place UNIQUE formula
  2. Next, we will use the SORT and UNIQUE functions 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.
    unique values sorted using SORT
  3. 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’.
    count duplicates in Google Sheets
  4. In the new column, we’ll add a COUNTIF formula. The first argument should reference our cell range. The second argument will contain a reference to the value we want to find duplicates of.
    use COUNTIF to count duplicates in Google Sheets
  5. 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.
    count duplicates in Google Sheets
  6. We can ignore counting the first instance by subtracting the result of our COUNTIF function by 1.
    subtract one to get actual number of duplicates
  7. Use the Fill Handle tool to return the duplicate counts of all unique values in the range.
    use Fill Handle to fill column D
  8. Lastly, we can compute the total number of duplicates by summing up the entire ‘Duplicate Count’ column.
    count total number of duplicates in Google Sheets

 

 

Frequently Asked Questions (FAQ)

  1. Can I count duplicates without case sensitivity?
    Yes, we can ignore the case of our target values by using the LOWER function to convert every character to lowercase.
    The example below shows values with both uppercase and lowercase variants. When returning UNIQUE values, we can first pass our data through the LOWER function.
    use LOWER function to ignore uppercase and lowercase values

 

 

This step-by-step guide should be all you need to count duplicates in Google Sheets. Our guide shows how to use the UNIQUE and 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. 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like