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

## Download Workbook

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

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: Count the Order of Occurrence of Duplicates in Excel (4 Methods)**

__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 Based on Multiple Criteria 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 Duplicates in Two Columns in Excel (8 Methods)**

**Similar Readings:**

**VBA to Count Duplicates in Range in Excel (4 Methods)****How to Count Repeated Words in Excel (11 Methods)****How to Ignore Blanks and Count Duplicates in Excel (3 Ways)**

__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: How to Count Duplicate Rows in Excel (4 Methods)**

__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: How to Count Duplicates in Column in Excel (3 Ways)**

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

**Read More: How to Count Duplicate Values Only Once in Excel (3 Ways)**

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

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