How to Count Duplicates in Two Columns in Excel (8 Methods)

Probably you have a larger dataset with duplicates and non-duplicates values and you need to count the number of duplicates. If you’re looking for such types of things, you’re here in the right place. In this tutorial, I’ll demonstrate 8 productive methods on how to count duplicates in two columns in Excel with proper explanation.

For your convenience, I would like to say that you’ll see the combined formula and output at first. Then you’ll get the necessary explanation sequentially.

How to Count Duplicates in Two Columns in Excel: 8 Methods

Let’s introduce today’s dataset where two lists of companies are provided. Column B depicts the List of US Companies by Revenue (List-1) whereas column D shows the List of US Companies by Profit (List-2). Now, I’ll count the number of duplicates across two columns.

Let’s explore the methods.

1. Enumerate Duplicates in Two Columns Using the COUNTIF Function

In the beginning method, you’ll see the use of the COUNTIF function, one of the most popular functions, to count the number of cells with criteria.

While counting duplicates, the generic formula will be like the following.

In the case of today’s dataset, the adjusted formula will be-

Here, $B$5:$D$12 is the range where I want to count duplicates, and B5 is the specific cell that I want to count (criteria).

If you look closely at the above output, you’ll find that the value of duplicates is 2. On the other hand, the value of unique records is 1. So, I can assign the logical IF function to return Duplicates instead of getting 2. The formula will be-

Here, If the value of the syntax COUNTIF($B$5:$D$12,B5 is greater than 1, the above formula will return Duplicates. Else it’ll return Unique.

Read More: How to Count Duplicates in Column in Excel

2. Count Duplicates in Two Columns Using the SUMPRODUCT and COUNTIF Functions

In the first method, we explored how to count duplicates across two columns based on rows. But if you want to count the number of duplicates of the total dataset along with two columns, you can combine the SUMPRODUCT function with the COUNTIF function. The combined formula will be-

Here, $B$5:$B$12 is the cell range representing the list of companies by revenue (List-1), and $D$5:$D$12 is the cell range depicting the list of companies by profit (List-2).

In the main formula, the COUNTIF function counts the number of duplicates separately. For example, the function returns 1 for the first row as depicted in the following image.

Then the SUMPRODUCT function sums up the values that were found using the COUNTIF function.

Read More: How to Count Duplicate Values in Multiple Columns in Excel

3. Utilizing the Combination of SUMPRODUCT and MATCH

Instead of using the COUNTIF function, we further can utilize the MATCH function together with the SUMPRODUCT function to count the number of duplicates for the whole dataset.

The combined formula for the F5 cell is as follows.

Here, the MATCH function returns the relative position of matched values. Also, the function gets #N/A when there exist unique records as shown in the following screenshot. So, the ISNUMBER function is used to return TRUE or FALSE to avoid #N/A mainly.

Then I used double dash (—) before the ISNUMBER function to convert the TRUE or FALSE (boolean values) into integer values. Look at the following picture to understand clearly.

Finally, the SUMPRODUCT sums up all values and returns 5 which is the number of duplicates.

Read More: Count Number of Occurrences of Each Value in a Column in Excel

4. Applying the Excel SUM and MATCH Functions to Count Duplicates in Two Columns

Again you can apply the SUM function rather than using the SUMPRODUCT function. The blended formula will be-

While explaining the formula I can say, the ISNA function omits the #N/A error found by the MATCH function. Furthermore, it returns TRUE for duplicate values and FALSE for unique values.

Later, I assigned the logical IF function to get 1 for the TRUE boolean value and 0 for the FALSE value. Just look at the following screenshot.

Ultimately, when I use the SUM function, it’ll return the number of duplicate values.

Read More: Count the Order of Occurrence of Duplicates in Excel

5. Counting Duplicates in Two Columns Using the VLOOKUP Function 

If you are accustomed to using the VLOOKUP function, you can further use the function to count duplicates in Excel. The formula will be-

Here, the TRUE is used as the col_index argument because there is only 1 column in today’s dataset.

As you see, the above formula returns #N/A. So, use the IFERROR function to avoid such type of output.

Again, if you want to get the Duplicates or Unique as the output instead of getting the values duplicate values exactly, you may use the logical IF function.

Lastly, if you want to count duplicates, just use the COUNTIF function.

The cell E14 in the above picture shows the number of duplicates.

Read More: How to Count Duplicates Based on Multiple Criteria in Excel

6. Employing the IF and MATCH Functions to Count Duplicates in Excel

Furthermore, you can employ the IF and MATCH functions combined to get the Duplicates or Unique values.

Here, B5 is the lookup value from List-1 and $D$4:$D$12 is the cell range for List-2.

Now, try a different way to count the duplicates.

➤ Firstly, click on the Filter option from the Sort & Filter ribbon in the Data tab keeping the cursor over the E4 cells.

➤ Secondly, press on the drop-down list and you’ll see such types of options as shown in the following picture.

So, check the box before Duplicates and uncheck the rest boxes.

Immediately, the output will be as follows.

➤ Lastly, you may use the SUBTOTAL function to get the number of duplicates.

Here, 3 refers to COUNTA that counts cells including the empty cells.

Read More: How to Count Occurrences Per Day in Excel

7. Counting Duplicates in Two Columns in Different Sheets

Let’s say you want to count the number of duplicates along with two different sheets. Here, the first sheet namely Company List by Revenue contains the List-1.

Whereas the second sheet namely Company List by Profit contains the List-2.

Now, insert the following formula in the C5 cell of the second sheet.

After pressing Enter and using the Fill Handle Tool, you’ll get the following output.

Moreover, if you want to count duplicates, just use the following formula.

8. A Unique Method for Microsoft 365 Users to Count Duplicates

If you’re a Microsoft 365 user and want to count duplicates in the case of similar rows, you can utilize the following formula.

Here, the B5:C12 is the cell range of List-1 and List-2.

If you press Enter, you’ll get the following output.

In the above picture, G8 and G11 return 2 because the value of their respective rows is duplicated.

In this method, the COUNTIFS function counts the number of duplicates for similar rows where the INDEX(UNIQUE(B5:C12),0,1) is the criterion for the first column that returns unique values. And the INDEX(UNIQUE(B5:C12),0,2) is the criterion for the second column.

However, I used the CHOOSE function finally as the index_num 1,2,3 to get the value for getting the list of 3 arguments.

Things to Remember

• If you’re not a Microsoft 365 user, don’t forget to press CTRL + SHIFT + ENTER for the array function.
• You can simply put the dollar sign ($) as an absolute cell reference by pressing the F4 key.

Download Practice Workbook

Conclusion

This is how you may count duplicates in two columns in Excel. I strongly believe this article will articulate your Excel journey. Anyway, if you have any queries or recommendations, please share them in the comments section.

<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel