If you are looking for some of the easiest ways to count duplicate values in multiple columns in Excel, then you will find this article useful. Counting duplicates in multiple columns is quite different from counting duplicates in a single column. So, letâ€™s explore the methods to count the duplicates easily.

**Table of Contents**Expand

## How to Count Duplicate Values in Multiple Columns in Excel: 6 Ways

Here, we have the sales records of a company containing the records for *January *and *February *and we will try to count the duplicate sales values between the ** January **and

**columns.**

*February*We have used *Microsoft Excel 365* version here, you can use any other versions according to your convenience.

__Method-1__: Using COUNTIF Function to Count Duplicate Values in Multiple Columns

Here, we will use the **COUNTIF function** to count the duplicate sales values between the ** January **and

**columns and so that we have added an extra column**

*February***Helper**.

** Steps**:

âž¤ Type the following formula in cell

**E4**

`=COUNTIF($C$4:$D$11,C4)`

Here, **$C$4:$D$11 **is the range, **C4 **is the criteria corresponding to the cell with the formula.

âž¤ Press **ENTER **and drag down the **Fill Handle **tool.

Then, you will get **2 **for the duplicate values in the two columns as you can see the duplicate values have been indicated with red, blue, and green color boxes.

Now, we will count the number of duplicate values by using the following formula

`=COUNTIF(E4:E11,2)`

Here, **E4:E11 **is the range and **2 **is the criteria for indicating duplicates.

After pressing **ENTER**, you will get ** 3 **as the number of the duplicate values between the

**January**and

**February**columns.

**Read More: **How to Count Duplicates in Column in Excel

__Method-2__: Combination of SUM, IF, ISNA, and MATCH Functions to Count Duplicate Values

In this section, we will be using the **SUM function**, **IF function**, **ISNA function**, and **MATCH function** to count the duplicate values in the ** January **and

**columns.**

*February*** Steps**:

âž¤ Type the following formula in cell

**D12**

`=SUM(IF(ISNA(MATCH($C$4:$C$11,$D$4:$D$11,0)),0,1))`

Here, **$C$4:$C$11 **is the range of the ** January **column and

**$D$4:$D$11**is the range of the

**column.**

*February*returns the relative position of the duplicate values in the range`MATCH($C$4:$C$11,$D$4:$D$11,0)`

â†’**$D$4:$D$11**corresponding to the range**$C$4:$C$11**.

**Output â†’**`{#N/A;3;#N/A;#N/A;8;#N/A;#N/A;4}`

returns`ISNA(MATCH($C$4:$C$11,$D$4:$D$11,0))`

â†’ ISNA**TRUE**for**#N/A**error otherwise**FALSE**

**Output â†’**`{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}`

return`IF(ISNA(MATCH($C$4:$C$11,$D$4:$D$11,0)),0,1)`

â†’ IF**0**for**TRUE**and**1**for**FALSE**

**Output â†’**`{0;1;0;0;1;0;0;1}`

becomes`SUM(IF(ISNA(MATCH($C$4:$C$11,$D$4:$D$11,0)),0,1))`

`SUM({0;1;0;0;1;0;0;1})`

**Output â†’**3

After pressing **ENTER**, you will get the total number of duplicate sales values as **3 **between the ** January **and

**columns.**

*February***Related Content: **How to Count Duplicates in Two Columns in Excel

__Method-3__: Using AND Function, COUNTIF Function to Count Duplicate Values in Multiple Columns

Here, we will use the **AND function**, **COUNTIF function** to count the duplicate sales values between the ** January **and

**columns.**

*February*** Steps**:

âž¤ Type the following formula in cell

**E4**

`=AND(COUNTIF($C$4:$C$11,C4),COUNTIF($D$4:$D$11,C4))`

Here, **$C$4:$C$11 **is the range of the ** January **column and

**$D$4:$D$11**is the range of the

**column.**

*February*returns the number of the value in cell`COUNTIF($C$4:$C$11, C4)`

â†’**C4**in the range**$C$4:$C$11**

**Output â†’**1

returns the number of the value in cell`COUNTIF($D$4:$D$11, C4)`

â†’**C4**in the range**$D$4:$D$11**

**Output â†’**0

becomes`AND(COUNTIF($C$4:$C$11,C4),COUNTIF($D$4:$D$11,C4))`

`AND(1,0)`

**Output â†’**FALSE

âž¤ Press **ENTER **and drag down the **Fill Handle **tool.

In this way, you will get **TRUE **for the duplicate values and **FALSE **for the unique values and now we will count the number of **TRUE **in the ** Helper **column to get the number of duplicate values.

âž¤ Type the following formula in cell **E12**

`=COUNTIF(E4:E11,TRUE)`

Here, **E4:E11 **is the range, and **TRUE **is the criteria.

âž¤ Press **ENTER**.

Finally, you will be able to count the duplicate values in the ** January **and

**columns which are**

*February***3**.

**Read More: **How to Count Duplicate Rows in Excel

__Method-4__: Using Excel SUMPRODUCT, COUNTIF Functions to Count Duplicate Values in Multiple Columns

You can use the **SUMPRODUCT function**, **COUNTIF function** to count the duplicate values in the columns; ** January **and

**.**

*February*** Steps**:

âž¤ Type the following formula in cell

**D12**

`=SUMPRODUCT(COUNTIF($C$4:$C$11,$D$4:$D$11))`

Here, **$C$4:$C$11 **is the range of the ** January **column and

**$D$4:$D$11**is the range of the

**column.**

*February*gives the matched number of cells between the ranges`COUNTIF($C$4:$C$11,$D$4:$D$11)`

**$C$4:$C$11**and**$D$4:$D$11**

**Output â†’**`{0;0;1;1;0;0;0;1}`

becomes`SUMPRODUCT(COUNTIF($C$4:$C$11,$D$4:$D$11))`

`SUMPRODUCT({0;0;1;1;0;0;0;1})`

**Output â†’**3

âž¤ Press **ENTER**.

Afterward, you will get the number of duplicate values in the ** January **and

**columns which is**

*February***3**.

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

__Method-5__: Combination of SUMPRODUCT, ISNUMBER, MATCH Functions to Count Duplicate Values

Here, we will use the **SUMPRODUCT function**, **ISNUMBER function**, **MATCH function **to determine the duplicates between the ** January **and

**columns.**

*February*** Steps**:

âž¤ Type the following formula in cell

**D12**

`=SUMPRODUCT(--(ISNUMBER(MATCH($C$4:$C$11,$D$4:$D$11,0))))`

**$C$4:$C$11 **is the range of the ** January **column and

**$D$4:$D$11**is the range of the

**column.**

*February*returns the relative position of the duplicate values in the range`MATCH($C$4:$C$11,$D$4:$D$11,0)`

â†’**$D$4:$D$11**corresponding to the range**$C$4:$C$11**.

**Output â†’**`{#N/A;3;#N/A;#N/A;8;#N/A;#N/A;4}`

becomes`ISNUMBER(MATCH($C$4:$C$11,$D$4:$D$11,0))`

`ISNUMBER({#N/A;3;#N/A;#N/A;8;#N/A;#N/A;4})`

**Output â†’**`{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}`

becomes`--(ISNUMBER(MATCH($C$4:$C$11,$D$4:$D$11,0)))`

Double negation (â€“) will convert`--({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE})`

â†’**TRUE**into**1**and**FALSE**into**0**

**Output**â†’`({0;1;0;0;1;0;0;1})`

becomes`SUMPRODUCT(--(ISNUMBER(MATCH($C$4:$C$11,$D$4:$D$11,0))))`

`SUMPRODUCT({0;1;0;0;1;0;0;1})`

**Output â†’**3

After pressing **ENTER**, you will get the total number of duplicate sales values between the ** January **and

**columns.**

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

__Method-6__: Using Conditional Formatting to Count Duplicate Values in Multiple Columns in Excel

Here, we will use **Conditional Formatting** to count the duplicate values in the **January **and **February **columns.

** Steps**:

âž¤ Select the

**and**

*January***columns.**

*February*âž¤ Go to

**Home**Tab >>

**Styles**Group >>

**Conditional Formatting**Dropdown >>

**Highlight Cells Rules**Option >>

**Duplicate Values**Option.

Then, the **Duplicate Values **wizard will appear.

âž¤ Select the **Custom Format **option in the second box.

Now, the **Format Cells **dialog box will open up.

âž¤ Select **Fill **Option, choose the *Yellow* Color, and press **OK**.

Again, press **OK **in the **Duplicate Values **dialog box.

In this way, the cells with duplicate values will be highlighted.

âž¤ Select any of the columns between ** January **and

**.**

*February*âž¤ Go to

**Home**Tab >>

**Editing**Group >>

**Find & Select**Dropdown >>

**Find**Option.

After that, the **Find and Replace **Dialog Box will pop up.

âž¤Select the **Format **Option.

Then, the **Find Format **Dialog Box will appear.

âž¤ Select **Fill **Option, choose the *Yellow* Color, and press **OK**.

Afterward, the following **Preview **section will appear.

âž¤ Click the **Find All **option.

Eventually, you will see the number of yellow color cells which is **3 **on the left bottom corner of the dialog box.

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

**Download Workbook**

## Conclusion

In this article, we tried to cover the ways to count duplicate values in multiple columns in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

**Related Articles**

- How to Count Occurrences Per Day in Excel
- How to Count Duplicates Based on Multiple Criteria in Excel

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