How To Count Duplicates in Google Sheets

Posted by Matthew on May 16, 2019

Spreadsheets are a very useful tool for organizing, sorting, manipulating, and generating reports from all sorts of data. 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.

Data can include anything from numbers to email addresses to any other types of data that you only want in your spreadsheet one time. You don’t want to double count when making calculations with numbers and often with email lists you’re uploading to your Email Service Provider, you don’t want duplicate email addresses.

Sometimes removing these duplicates is necessary for processing the data in the spreadsheet, but other times we don’t want to change the data but do 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 Sheets then how to resolve duplicate data issues in Google Sheets.

In this article, you’ll learn to count duplicates using built-in functions and you’ll learn to find and remove duplicates using an essential add-on for Google Sheets power users called Power Tools.

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)`.

Now 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 much the same as numerical data. Replace the function’s numerical criterion with text. 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. That 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.

Consequently, 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 add it to the application 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.

Note: “Dedupe and Compare” used to be called “Data.” Despite the feature name change, Dedupe and Compare perform the same deduping and comparison operations as Data did.

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.

Thus, the add-in 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.

As you can see, there are numerous ways to count or highlight duplicates in Sheets spreadsheets with functions and Power Tools, an essential add-on for any Google Sheets power user. If you use Google Sheets with any regularity, you’ll find Power Tools will quickly become an essential part of your Google Sheets toolkit. You can also automate processes such as finding and resolving duplicate data issues using Google Apps scripts. which is a powerful tool for advanced Google Apps users who feel comfortable doing some coding.

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.

One thought on “How To Count Duplicates in Google Sheets”

John Smith says:
COUNT doesn’t return number of duplicates. COUNTIF returns #ERROR!

Leave a Reply to John Smith Cancel reply

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

Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.