Microsoft Excel is the most useful data-tracking software. For datasets that have numerous worksheets or users or identity numbers, it is difficult to analyze at ease. In cases of data tracking, there are significant examples where duplicate data exists. Data analysts mostly evaluate this repetition to avoid data overlap. There are also cases where you may only need the total count that has duplicate values. So, we can say that, if you want to count duplicate values for multiple columns in Excel, it is a complex problem since there is no absolute formula to compare and count altogether.

This article can guide you to eight different techniques. All the methods have step-by-step instructions with a figurative description.

Following is an image where you can see the methods of how you can easily count duplicate values in multiple columns in Excel for a given dataset.

## 📁 Download Excel File

Download the Excel file below.

## Learn to Count Duplicate Values in Multiple Columns in Excel with These 8 Approaches

This article is going to introduce eight approaches to count duplicate values in multiple columns in Excel. The methods will help you to understand the uses elaborately. The following discussion will cover the uses of the **COUNTIF**, **SUMPRODUCT**, **MATCH**, and **SUM** functions to directly count the duplicates. You will also get complex methods, you can use the **AND**, **SUBTOTAL**, and **VLOOKUP** functions. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.

**approach**

### 1. Application of COUNTIF Function to Count Duplicate Values in Multiple Columns in Excel

**The COUNTIF function** is applicable for counting cell numbers.

**COUNTIF(cell_range,criteria)**

**cell_range –** cell range where you want to search from.

**criteria –** cell value or constant value which you want to count.

It counts the number of cells from the cell range that follows the criteria. We can use this method to directly count duplicate values in multiple columns in Excel. Although you need to input the data set manually, it is the only method where you can do the job using one function only.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, create a table below the dataset with the data for counting reference. Here, we inserted the player names manually.

- Now, select cell
**C13**and enter the following formula.

**=COUNTIF($B$6:$D$10,B13)**

**🔨 Formula Breakdown**

**COUNTIF($B$6:$D$10,B13)**

👉 Here, **COUNTIF** returns the cell numbers that have **B13** data from **B6:D10**.

- Then, using the
**Fill Handle**icon, copy the formula from**C13**to**C22**.

- After that, select cell
**D13**and enter the following formula.

`=COUNTIF(C13:C22,">1")`

**🔨 Formula Breakdown**

**COUNTIF(C13:C22,”>1″)**

👉 Here, **COUNTIF** returns the cell numbers that have values larger than **1** from **C13:C22**.

**👉 COUNTIF(C13:C22,”>1″)** adds both the number of cells and counts total duplicates.

Finally, you get **3** as output which resembles that the dataset has **3** duplicate inputs.

**approach**

### 2. Combination of AND and COUNTIF Functions to Count Duplicates in Multiple Columns

**The AND function** is convenient to compare cell numbers for every column under the criteria. The **COUNTIF** function counts the output from the **AND** function.

**AND(criteria1, criteria2, [criteria3], ..)**.

**criteria1 –** The first cell reference or criteria that you want to tag along with the next one(s).

**criteria2 –** The second cell reference or criteria that you want to add with the rest of the argument (s).

The **AND** function can bring together multiple cells as this is the only method that can confirm duplicate values for every column. For the dataset in this method, we have shown the use of the **AND** function for three columns. Although you can use this method for any number of columns. You need to understand that it only gives output true if there are duplicates in all of them. If we want to count duplicate values for multiple columns in Excel even if all the columns do not have duplicate values, we can use **the OR function**. This formula doesn’t need individually organized values beforehand like the **first method**.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cell
**E6**and enter the following formula here.

`=IF(AND(B6=C6,C6=D6,D6=B6), "Duplicate","No Duplicate")`

**🔨 Formula Breakdown**

**IF(AND(B6=C6,C6=D6,D6=B6), “Duplicate”,”No Duplicate”)**

👉 Here, **AND** returns **TRUE** if the data on **B6**, **C6**, and **D6** all cells are same; **FALSE** otherwise.

👉 **IF** shows “**Duplicate**” for the return of **AND** as **TRUE**; “**Not Duplicate**” otherwise.

- Then, using the
**Fill Handle**icon, copy the formula from**E6**to**E10**.

- After that, select cell
**E11**and enter the following formula.

`=COUNTIF(E6:E10,"Duplicate")`

**🔨 Formula Breakdown**

**COUNTIF(E6:E10,”Duplicate”)**

👉 Here, **COUNTIF** returns the cell numbers that have “**Duplicate**” data from **E6:E10**.

Finally, you get **1** as output which resembles that the dataset has **1** row that has the same inputs for all **3** columns.

**N.B.:** Even if row **7** has two “**Pedri**”, the method returns “**No Duplicate**”. It occurs since the **AND** function returns “**Duplicate**” only when all three columns have the same value. To get a “**Duplicate**” return we can use the following formula.

`=IF(OR(B6=C6,C6=D6,D6=B6), "Duplicate","No Duplicate")`

**approach**

### 3. Combine SUMPRODUCT and COUNTIF Functions to Determine Duplicate Values in Multiple Columns

**The SUMPRODUCT function** returns the sum of the specific ranges’ products. You can do addition, subtraction, and division apart from multiplication as well.

**SUMPRODUCT(array1, [array2], ..)**

**array1 –** Here you add the first array argument with the data that you want to multiply and then add.

**array2 –** It is for the same contribution as **array1** and is optional for use for the function.

If you combine the **SUMPRODUCT **and the **COUNTIF** functions, you can easily determine total duplicate values in multiple columns in Excel. The formula counts the duplicate values first. Then, the **SUMPRODUCT** eventually counts the total duplicate values in multiple columns. Unlike, the previous two methods, this method is applicable to two columns only and can count the number directly.

⬇️⬇️ **STEPS **⬇️⬇️

- To begin with, select cell
**D6**and enter the following formula here.

**=SUMPRODUCT(COUNTIF($B$6:$B$10,$C$6:$C$10))**

**🔨 Formula Breakdown**

**SUMPRODUCT(COUNTIF($B$6:$B$10,$C$6:$C$10))**

👉 Here, **COUNTIF** returns the cell numbers that have **C6:C10** data from **B6:B10**.

👉 Also, **SUMPRODUCT** adds and multiplies returns from the **COUNTIF** function.

Therefore, we get **2** since there are **Sergi** and **Pedri** from two columns as duplicates.

**approach**

### 4. Excel MATCH Function to Count Duplicate Values

**The MATCH function** searches for a declared value in a cell range and returns its position in the range.

**MATCH(lookup_value, lookup_array, [match_type])**

**lookup_value –** This is usually the cell reference or absolute value that should match with the **lookup_array**.

**lookup_array –** This argument takes a cell range, where the value is searched.

**match_type –** Although an optional part, helps in case of relative matches by using **0** as an argument, **1** otherwise as default i.e. exact match.

The **MATCH** function matches the data of one column to another and gives an exact or relative match. From the position return, you can use **the ISNUMBER function** to make sure the position is valid. Finally, the **SUMPRODUCT** function will help you to count the total duplicate values in multiple columns in Excel. This method works almost as efficiently as **the third method**.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, select cell
**D6**and enter the following formula here.

**=SUMPRODUCT(–(ISNUMBER(MATCH(B6:B10,C6:C10,0))))**

**🔨 Formula Breakdown**

**SUMPRODUCT(–(ISNUMBER(MATCH(B6:B10,C6:C10,0))))**

👉 Here, **MATCH** looks for **B6:B10** data range from data range **C6** to **C10**. It returns the search value.

👉 Also, **ISNUMBER** returns only the number values from the output of the **MATCH** function.

👉 Finally, **SUMPRODUCT** adds and multiplies returns from the **ISNUMBER** function.

Moreover, the result comes out as **2** since the formula gets two sets of data as duplicates.

**approach**

### 5. Merging SUM and MATCH Functions to Count Duplicate Values in Multiple Columns

**The SUM function** adds individual values, cell references, ranges, or a mix of all three. The **MATCH** function searches for data or criteria for a specific cell range.

**SUM(number1,[number2],…)**

**number1 –** Here, you should use a cell reference or cell range to do the sum operation.

**number2 –** This argument is optional and helpful when non-adjacent cells are up for summation.

The **MATCH** function provides the position of data and **the ISNA function** confirms the validity of the position. Here, you can use **the IF function** to apply conditions and how you want to get the data. Finally, the **SUM** function adds all the returns from the **IF** function formula. Although this method uses four functions, it is easier to understand the structure than the previous method. As a result, if you have a complex dataset, this method can help you to successfully modify and count duplicate values.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cell
**D6**and enter the following formula here.

**=SUM(IF(ISNA(MATCH(B6:B10,C6:C10,0)),0,1))**

**🔨 Formula Breakdown**

**SUM(IF(ISNA(MATCH(B6:B10,C6:C10,0)),0,1))**

👉 Here, **MATCH** looks for **B6:B10** data range from data range **C6** to **C10**. It returns the exact match value.

👉 Also, **ISNA** returns **FALSE **if there is no error. In case of an error, it returns **TRUE**.

👉 Again, **IF** returns **0** or **1** according to the condition from the **ISNA** function.

👉 Finally, **SUM** adds returns from the **IF** function.

Finally, you can see in the image that the output is **2**.

**approach**

### 6. VLOOKUP Functionto Count Duplicate Values in Multiple Columns

**The VLOOKUP function** can find things in a table or a range by row. It can work across different worksheets as well.

**VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])**

**lookup_value –** You should add the cell reference or absolute data that you need to look for.

**table_array –** The **lookup_value** must be present within this cell range or it will show an error.

**col_index_num –** A specified column number should be here within the range where the value will return.

**range_lookup –** It returns an approximate or exact match according to commands as **1/TRUE** or **0/FALSE**.

As we discussed above regarding the **VLOOKUP** function, it looks for cell values as the formula. **The ISERROR function** provides “**FALSE**” if any duplicate is found and “**TRUTH**” otherwise. Since our motive is to count the duplicate values in multiple columns, we can just count the “**FALSE**” returns under the “**Not Duplicates**” column. Now, the only confusion in this method causes due to the **ISERROR** functionality. Apart from that we can modify the **VLOOKUP** function criteria and range into a more diverse and efficient formula to count duplicate values in Excel for multiple columns using this method.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, select cell
**D6**and enter the following formula here.

**=ISERROR(VLOOKUP(B6,$C$6:$C$10,1,0))**

**🔨 Formula Breakdown**

**ISERROR(VLOOKUP(B6,$C$6:$C$10,1,0))**

👉 Here, **VLOOKUP** looks for **B6** from cell range **C6:C10**.

👉 Furthermore, **ISERROR** refers to the error value.

- Next, copy the formulas down their cells using the
**Fill Handle**icon.

- Next, select cell
**E6**and enter the following formula here.

`=COUNTIF(D6:D10,`

`"FALSE")`

**🔨 Formula Breakdown**

**COUNTIF(D6:D10,****“FALSE”)**

👉 Here, **COUNTIF** returns the cell numbers that have “**FALSE**” data from **D6:D10**.

Therefore, you should get the count output as **2** since there are two sets of duplicate values in multiple columns in the Excel file.

**approach**

### 7. Apply Filter and SUBTOTAL Function

The **Filter** option is one of the most outstanding tools in Microsoft Excel. On the other hand, **the SUBTOTAL function** is an automatic update addition function that aligns with our mission to count duplicate values in multiple cells in Excel.

**SUBTOTAL(function_num,ref1,[ref2],…)**

**function_num –** It varies according to the function required for the references. For example, we have used **3** to represent **the COUNTA function**.

**ref1 –** This argument belongs to the cell range where you want the previous function to be in action.

**ref2 –** This argument is optional and denotes cell references as well.

Before using the **Filter** tool, you can use a formula to declare which of the rows provide duplicate values. For that, you can merge the **IF**, **ISNUMBER**, and **MATCH** functions. We discussed functionality for most of these functions in the previous methods. The returns can work under the **Filter **tool and the **SUBTOTAL** function will auto-update its result for the filtered dataset. This method is useful for large datasets and easy to update any time you want without modifying the formula like the previous methods.

⬇️⬇️ **STEPS **⬇️⬇️

- To begin with, select cell
**D6**and enter the following formula here.

`=IF(ISNUMBER(MATCH(B6,$C$6:$C$10,0)),"Duplicate","Unique")`

**🔨 Formula Breakdown**

**IF(ISNUMBER(MATCH(B6,$C$6:$C$10,0)),”Duplicate”,”Unique”)**

👉 Here, **MATCH** looks for **B6** data range from data range **C6** to **C10**. It returns the exact match value.

👉 Also, **ISNUMBER** returns the number values from the **MATCH** function.

👉 Again, **IF** returns “**Duplicate**” or “**Unique**” according to the condition from the **ISNUMBER** function.

- Next, copy the formulas down their cells using the
**Fill Handle**icon.

- After that, select cell
**E6**and enter the following formula here.

**=SUBTOTAL(3,D6:D10)**

**🔨 Formula Breakdown**

**SUBTOTAL(3,D6:D10)**

👉 Here, **SUBTOTAL** returns the count value by using **3** as the **COUNTA** function from data range **D6** to **D10**.

- Now, select cell range
**D5:D10**. - Here, choose the
**Filter**option from the**Sort & Filter**drop-down, under the**Editing**group of the**Home**tab.

- Here, click on the
**drop-down**icon available beside the column header like the following image. - Now, uncheck the
**Unique**option from the**Filter**drop-down. - After that, hit the
**OK**button.

Moreover, you get the total count for the duplicate values for multiple columns in Excel.

**approach**

### 8. Count Duplicate Values in Multiple Columns by Using UNIQUE Function in Microsoft Office 365

For users of previous versions of Microsoft Excel, this method isn’t applicable since it is only possible for **Microsoft Office 365** and for a few other latest versions for now. Now, **the UNIQUE function** returns a range of unique values for a specific dataset just as it were.

**UNIQUE(array,[by_col],[exactly_once]) **

**array –** That is for the source cell range, from where you will extract the unique values.

**by_col –** This argument is optional whereas **TRUTH** means to compare columns against columns and **FALSE** means to compare rows against rows.

**exactly_once –** This optional argument needs **TRUTH** in its syntax if you want to consider duplicate values once, otherwise **FALSE** to consider them each of the times.

The **UNIQUE** function is helpful to generate a table with unique values. Now, to navigate it to count duplicate values in multiple columns you need to incorporate it with the **CHOOSE**, **INDEX**, and **COUNTIFS** functions. These will help you to distribute, position, and count respectively. No other methods can generate a new table and declare if duplicate or unique. After that, you can simply use the **COUNTIF** function to count the total duplicate values in multiple columns in Excel.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, select cell
**E6**and enter the following formula.

**=CHOOSE({1,2,3},UNIQUE(B6:C10),INDEX(UNIQUE(B6:C10),0,2),COUNTIFS(B6:C10,INDEX(UNIQUE(B6:C10),0,1),B6:C10,INDEX(UNIQUE(B6:C10),0,2)))**

**🔨 Formula Breakdown**

**CHOOSE({1,2,3},UNIQUE(B6:C10),INDEX(UNIQUE(B6:C10),0,2),COUNTIFS(B6:C10,INDEX(UNIQUE(B6:C10),0,1),B6:C10,INDEX(UNIQUE(B6:C10),0,2)))**

👉 Here, **UNIQUE(B6:C10)** returns all the unique cells present within cell range **B6:C10**.

👉 **INDEX(UNIQUE(B6:C10),0,1)** and **INDEX(UNIQUE(B6:C10),0,2)** calls the returns of **UNIQUE** function. The formula is done by **Columns E** and **F** respectively.

👉 **COUNTIFS** counts cells if the condition from the **INDEX** function checks out for another column as a duplicate for range **B6:C10**.

👉 Finally, **CHOOSE** distributes column IDs for placing the data.

- As visible from the picture, the entire dataset and duplicate countings are automatically available here.

- Now, to count the total duplicates, select cell
**G11**and enter the following formula.

**=COUNTIF(G6:G10,2)**

**🔨 Formula Breakdown**

**COUNTIF(G6:G10,2)**

👉 Here, **COUNTIF** returns the cell numbers that have **2** data from **G6:G10**.

Finally, you will be able to count duplicate values for multiple columns in Excel using this method.

## Use of Conditional Formatting to Highlight Duplicate Values in Multiple Columns

The **Conditional Formatting** option is one of the easiest tools to showcase data or cells fulfilling a specific criterion. Here, we will share how you can highlight all the duplicate values. Visually, it gets easy for users to understand the presence of duplicates. You can even count them using the **Find** tool for fewer sets of columns. You can even modify the cell format with their fill, font, border, etc. to highlight duplicate values.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select the cell range
**B6:D10**. - Now, select the
**Duplicate Values**option from the**Highlight Cells Rules**menu under the**Conditional Formatting**drop-down of the**Home**tab.

- Here, you will get the
**Duplicate Values**option. - Then, you can modify the format if you wish from the
**values with**drop-down. - Next, process with the
**OK**button.

Therefore, you should get an output with the **Light Red Fill** format and get the table output like the following picture.

## How to Count Duplicates for Multiple Worksheets

There are cases where you need to generate data across multiple sheets. Now, if your job is to count duplicate values for multiple columns in Excel for different worksheets, you can follow this method with ease. This method includes a formula with the **IF**, **ISERROR**, and **VLOOKUP** functions to declare the duplicate and unique values. The return is eventually useful for the **COUNTIF** function to add the total count.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, create a dataset for the “
**2022-2023**” worksheet where the first column is available.

- Next, select cell
**C6**from the “**2021-2022**” worksheet with the second column and enter the following formula.

`=IF(ISERROR(VLOOKUP(B6,'2022-2023'!$B$6:$B$10,1,0)),"No","Yes")`

**🔨 Formula Breakdown**

**IF(ISERROR(VLOOKUP(B6,’2022-2023′!$B$6:$B$10,1,0)),”No”,”Yes”)**

👉 Here, **VLOOKUP** looks for **B6** from cell range **B6:B10** of worksheet “**2022-2023**”. The return gets to the **ISERROR** function.

👉 Furthermore, **ISERROR** refers to the error value.

👉 Again, **IF** returns “**No**” or “**Yes**” according to the condition from the **ISERROR** function.

- Here, copy the formulas down their cells using the
**Fill Handle**icon.

- After that, select cell
**D6**and enter the following formula here.

`=COUNTIF(C6:C10,`

`"Yes")`

**🔨 Formula Breakdown**

**COUNTIF(C6:C10,****“Yes”)**

👉 Here, **COUNTIF** returns the cell numbers that have “**Yes**” data from **C6:C10**.

Finally, you can count duplicate values for multiple columns across the sheets in Excel.

## 📄 Important Notes

🖊️ This article has multiple methods to count duplicate values for two and more than two columns separately. Apart from methods **1** and **2**, you can’t use other methods for more than two columns in Excel.

🖊️ Many of the methods are available to count duplicate values in two steps and **3** methods to count automatically.

## 📝 Takeaways from This Article

The article lets the readers understand the variety of options available to count duplicate values in multiple columns in Excel.

📌 Primarily, you can count duplicates in two columns using the **COUNTIF **and** SUM** functions in Excel.

📌 You can combine the **AND **and **COUNTIF** functions to count duplicate values in all of the multiple columns in Excel. It is the only method that shows **Duplicate** when all the corresponding columns have duplicate values.

📌 Also, you can use the **SUMPRODUCT **function with the **COUNTIF** function to count duplicate values in multiple columns in Excel. You can directly get the total number of duplicates.

📌 You can employ **MATCH** and** ISNUMBER** functions as well.

📌 Then, you can count duplicates in two columns using the **SUM** and **MATCH **functions in Excel. You should add **IF** and **ISNA** functions with it for better functionality.

📌 You can employ the **VLOOKUP** function as well.

📌 Also, you can use the **Filter **option with the **SUBTOTAL** function to count duplicates in two columns in Excel. You can directly get the total number of duplicates.

📌 Here, **Microsoft Office 365** can also count duplicate values in multiple columns using the **UNIQUE** function.

## Conclusion

To count duplicate values, eight methods are available. You can use the **COUNTIF**, **SUMPRODUCT**, **MATCH**, and **SUM** functions to directly count the duplicates. For step-by-step complex methods, you can use the **AND**, **SUBTOTAL**, and **VLOOKUP** functions. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow **ExcelDen** to get more access to solutions regarding your Excel problems.