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

Column B depicts the List of US Companies by Revenue (List-1) while column D shows the List of US Companies by Profit (List-2). We’ll count the number of duplicates across two columns.

Dataset of excel count duplicates in two columns


Method 1 – Count Duplicates in Two Columns Using the COUNTIF Function

The generic formula is:

=COUNTIF(Range, Criteria)

  • Use the following formula:

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

$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

  • The value of duplicates is 2 or higher. The value of unique records is 1.
  • We can wrap the logical IF function to return Duplicates instead of getting a number:

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

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

Using the COUNTIF Function

Note that this formula only checks the values from one column, but counts their occurrences in both.

Read More: How to Count Duplicates in Column in Excel


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

  • Use the following formula:

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

$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

The COUNTIF function counts the number of duplicates separately. 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

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


Method 3 – Utilizing the Combination of SUMPRODUCT and MATCH

  • 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

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

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

We used a double dash () before the ISNUMBER function to convert TRUE or FALSE (boolean values) into integer values.

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

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


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

  • Use the following formula:

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

Applying the SUM and MATCH Functions

The ISNA function omits the #N/A error found by the MATCH function. It returns TRUE for duplicate values and FALSE for unique values.

We assigned the logical IF function to get 1 for the TRUE boolean value and 0 for the FALSE value.

Applying the SUM and MATCH Functions

The SUM function will return the number of duplicate values.

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


Method 5 – Counting Duplicates in Two Columns Using the VLOOKUP Function 

  • Use the following formula:

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

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

Using the VLOOKUP Function

The above formula returns #N/A. The IFERROR function avoids that output.

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

Using the VLOOKUP Function

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

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.

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


Method 6 – Using the IF and MATCH Functions to Count Duplicates in Excel

  • Use this formula:

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

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

  • Select E4.
  • Click on the Filter option from the Sort & Filter ribbon in the Data tab.

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

  • Click on the drop-down list and you’ll options.
  • Check the box before Duplicates and uncheck the others.

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

The output will be as follows.

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

  • Use the SUBTOTAL function to get the number of duplicates.

=SUBTOTAL(3,E5:E12)

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

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

Read More: How to Count Occurrences Per Day in Excel


Method 7 – Counting Duplicates in Two Columns in Different Sheets

We want to count the number of duplicates along with two different sheets. The sheet Company List by Revenue contains the List-1.

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

The second sheet Company List by Profit contains the List-2.

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

  • 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

  • Press Enter and use the Fill Handle Tool.

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

  • If you want to count duplicates, use the following formula.

=COUNTIF(C5:C12,"Duplicates")

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


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

  • Use 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)))

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.

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

We used the CHOOSE function 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, press CTRL + SHIFT + ENTER to apply an array function.
  • You can put the dollar sign ($) as an absolute cell reference by pressing the F4 key while selecting a cell or range.

Download the Practice Workbook


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

2 Comments
    • Hello Me,

      You are most welcome. Your appreciation means a lot to us. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo