How To Count Duplicates in Google Sheets

Last Updated on September 6, 2023 by Jake Sheridan

In this tutorial, you will learn how to count duplicates in Google Sheets.

How To Count Duplicates in Google Sheets

When working with data in Google Sheets, it is often helpful to know how many duplicate entries there are in a dataset. This can be done easily in Google Sheets using the COUNTIF function.

For example, let’s say you have a list of names, and you want to know how many of them are duplicates. The COUNTIF function can help you quickly count the number of duplicates in your dataset.

In this guide, we will show you how to use the COUNTIF function to count duplicates in Google Sheets. We will also show you how to find all unique values in a data range.

How to Count Duplicate Cells in Google Sheets

Here’s our step-by-step guide on how to count duplicate cells in Google Sheets.

Step 1

To start counting duplicates, we’ll need to know all the unique values in our range. We can use the UNIQUE function given a data range to return all unique values found in that range.

Step 2

We’ll use the COUNTIF function to identify the number of duplicates each unique value has in the original data range.

However, we’ll need to subtract 1 from the result of COUNTIF to account for the first occurrence of the text.

Step 3

Use this formula for each unique value to see how many times each value is duplicated in the range.

Step 4

We can use the SUM function to count the total number of duplicates.

Summary

This guide should be everything you need to learn how to count duplicates in Google Sheets.

Click this link to make a copy of this sample document to try doing it yourself!