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.


Download Practice Workbook


8 Methods to Count Duplicates in Two Columns in Excel

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.

Dataset of excel count duplicates in 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.

=COUNTIF(Range, Criteria)

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

 =COUNTIF($B$5:$D$12, B5)

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

Using the COUNTIF Function

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-

=IF(COUNTIF($B$5:$D$12,B5)>1, "Duplicates","Unique")

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.

Using the COUNTIF Function


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-

=SUMPRODUCT(COUNTIF($B$5:$B$12,$D$5:$D$12))

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

excel count duplicates in two columns Using the SUMPRODUCT and COUNTIF Functions

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.

excel count duplicates in two columns Using the SUMPRODUCT and COUNTIF Functions

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


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.

=SUMPRODUCT(--(ISNUMBER(MATCH(B5:B12,D5:D12,0))))

excel count duplicates in two columns Utilizing the Combination of SUMPRODUCT and MATCH

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.

excel count duplicates in two columns Utilizing the Combination of SUMPRODUCT and MATCH

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.

excel count duplicates in two columns Utilizing the Combination of SUMPRODUCT and MATCH

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


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-

=SUM(IF(ISNA(MATCH(B5:B12,D5:D12,0)),0,1))

Applying the SUM and MATCH Functions

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.

Applying the SUM and MATCH Functions

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


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-

=VLOOKUP(B5:B12,D5:D12,TRUE,FALSE)

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

Using the VLOOKUP Function

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

=IFERROR(VLOOKUP(B5:B12,D5:D12,TRUE,FALSE),"")

Using the VLOOKUP Function

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.

=IF(ISERROR(VLOOKUP($B$5:B$12,$D$5:$D$12,TRUE,FALSE)),"Unique","Duplicates")

Using the VLOOKUP Function

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

=COUNTIF(E5:E12,"Duplicates")

Using the VLOOKUP Function

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


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.

=IF(ISNUMBER(MATCH(B5,$D$4:$D$12,0)),"Duplicates","Unique")

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

excel count duplicates in two columns Employing the IF and MATCH Functions

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.

excel count duplicates in two columns Employing the IF and MATCH Functions

➤ 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.

excel count duplicates in two columns Employing the IF and MATCH Functions

Immediately, the output will be as follows.

excel count duplicates in two columns Employing the IF and MATCH Functions

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

=SUBTOTAL(3,E5:E12)

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

excel count duplicates in two columns Employing the IF and MATCH Functions


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.

excel count duplicates in two columns Counting Duplicates in Two Columns in Different Sheets

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

excel count duplicates in two columns Counting Duplicates in Two Columns in Different Sheets

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

=IF(ISERROR(VLOOKUP(B5,'Company List by Revenue'!$B$5:$B$12,1,0)),"Unique","Duplicates")

excel count duplicates in two columns Counting Duplicates in Two Columns in Different Sheets

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

excel count duplicates in two columns Counting Duplicates in Two Columns in Different Sheets

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

excel count duplicates in two columns Counting Duplicates in Two Columns in Different Sheets

=COUNTIF(C5:C12,"Duplicates")

excel count duplicates in two columns Counting Duplicates in Two Columns in Different Sheets


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.

=CHOOSE({1,2,3},UNIQUE(B5:C12),INDEX(UNIQUE(B5:C12),0,2), COUNTIFS(B5:C12,INDEX(UNIQUE(B5:C12),0,1),B5:C12,INDEX(UNIQUE(B5:C12),0,2)))

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

excel count duplicates in two columns A Unique Method for Microsoft 365 Users

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

excel count duplicates in two columns A Unique Method for Microsoft 365 Users

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.

excel count duplicates in two columns A Unique Method for Microsoft 365 Users

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.

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.

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo