How To Count Duplicates in Google Sheets

Many people use cloud spreadsheet applications such as Google Sheets to analyze their data and commonly run into the problem of duplicate data. Duplicated data means multiple instances of the exact same data where there should be only one instance.

Sometimes removing these duplicates is necessary for processing the data in the spreadsheet, but other times we simply want to know how many times a particular value is duplicated in our data.

In this article, I will show you several different ways to count duplicates in Google Sheets and how to remove them.

How To Count Duplicates in Google Sheets

There are several methods you can use to count and remove duplicates in Google Sheets.

For this article, we’ll take a look at how you can use the COUNTIF, COUNT, and COUNTA functions or the Power Tools add-on to accomplish this task.

Count Duplicates with COUNTIF

COUNTIF is a relatively basic Google Sheets function that counts cells that include numbers or text based on a specified condition. The syntax is simple; you only need to provide a cell range and criterion for which cells to count. You can enter the COUNTIF function in the fx bar with the syntax: ‘=COUNTIF(range, criterion).’

First, let’s set up a spreadsheet with some dummy data that we can include in a COUNTIF function. Open a blank spreadsheet in Google Sheets and enter the values ‘450,’ ‘350,’ ‘560,’ ‘450,’ ‘350,’ and ‘245’ in the cell range A2:A7.

Your spreadsheet should then be exactly the same as the one shown directly below:

To add the COUNTIF function to the spreadsheet, select cell B9 and click in the fx bar. Enter ‘=COUNTIF(A2:A7, “450”)‘ in the fx bar, and press the Return key to add the function to the cell. Cell B9 will now include the value 2. As such, it counts two duplicate ‘450’ values within the A2:A7 cell range.

COUNTIF also counts duplicate text strings. Simply replace the function’s numerical criterion with text to do so.

For example, enter ‘text string’ in cells A8 and A9 of your spreadsheet. Then, input the function ‘=COUNTIF(A2:A9, “text string”)‘ in cell B10.

B10 will then count two cells that include duplicate text as in the snapshot below:

You can also add a formula to a spreadsheet that counts multiple duplicate values within a single cell range. That formula adds two or more COUNTIF functions together.

As an example, enter the formula ‘=COUNTIF(A2:A7, “450”)+COUNTIF(A2:A7, “350”)‘ in the cell B11. This counts both the ‘450’ and ‘350’ duplicate numbers within column A. As a result, B11 returns the value 4 as in the snapshot directly below.

Count Duplicates with COUNT and COUNTA

COUNT is another function that can count duplicate values in spreadsheet cell ranges. However, you can only include cell ranges in this function. As such, COUNT isn’t much good when you have sheets with duplicate values scattered across lots of separate cell ranges within columns or rows. The function is much more effective for counting duplicates when you sort data in rows and columns.

Right-click the Column A header in the Sheets spreadsheet, and select the Sort sheet A-Z option. This will organize your column cells in numerical order with the lowest numbers at the top and highest values at the bottom as in the snapshot directly below. This also groups all the duplicate values together within single-cell ranges.

Now, you only need to enter one cell reference in the COUNT function for it to count all the duplicate values within the range.

For example, enter ‘=COUNT(A2:A3)‘ in cell B12 of your Sheets spreadsheet. B12’s COUNT function will then return the value 2, which is the number of duplicates within the range A2:A3.

The Sort sheet A-Z option also groups duplicated text in rows and columns within single-cell ranges. However, COUNT only works for numerical data.

For duplicated text, add the COUNTA function to the spreadsheet instead. As an example, input ‘=COUNTA(A7:A8)‘ in B13 of your spreadsheet, which will count the duplicate text string cells as shown below.

Count All Duplicates with Power Tools

Power Tools is a Google Sheets add-on that has lots of handy tools. You can download it from this page.

Power Tools includes a Remove duplicates option that can find all duplicate values and text within a selected cell range. As such, you can utilize that tool to count all the duplicated cell content in a selected column or row.

Open the Dedupe and Compare feature in Power Tools by selecting Power Tools from the Add-ons pulldown menu, then selecting the Dedupe and Compare option.

Click the cell reference button to select the cell range A1:A8, and press the OK option. Click Next and select the Duplicates + 1st occurrences option.

Click the Next button again to open the options shown directly below. Select the Column checkbox options there, and then click Next again.

Select the Add a status column radio button, which adds a new column highlighting duplicate values to the spreadsheet. There’s also a Fill Color option that you can select to highlight the duplicate cells with colors. When you press the Finish button, the add-in tells you how many duplicates are within the selected cell range.

The add-on counts all six duplicates within the spreadsheet’s cell range. That includes a couple of ‘350’ and ‘450’ values and the text string cells. Your sheet will also include a new B column highlighting the A rows with the duplicates as shown below.

Final Thoughts

Dealing with duplicate data in Google Sheets can be tricky; however, by using the functions listed above or an add-on like Power Tools, finding, analyzing, and removing duplicate data is quick and easy to do.

If you found this article helpful, you might also like this TechJunkie how-to article on How to get absolute value in Google Sheets.  If you have any Google Sheets tips and tricks, please post them in the comments below.