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.

**Table of Contents**hide

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

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

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

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

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.

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

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.

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

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.

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

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),"")`

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

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

`=COUNTIF(E5:E12,"Duplicates")`

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

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.

`=SUBTOTAL(3,E5:E12)`

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

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

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

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.

`=COUNTIF(C5:C12,"Duplicates")`

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

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.

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