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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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 February columns and so that we have added an extra column 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.

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

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

• `MATCH(\$C\$4:\$C\$11,\$D\$4:\$D\$11,0)` â†’ returns the relative position of the duplicate values in the range \$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}`
• `ISNA(MATCH(\$C\$4:\$C\$11,\$D\$4:\$D\$11,0))` â†’ ISNA returns TRUE for #N/A error otherwise FALSE
Output â†’ `{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}`
• `IF(ISNA(MATCH(\$C\$4:\$C\$11,\$D\$4:\$D\$11,0)),0,1)` â†’ IF return 0 for TRUE and 1 for FALSE
Output â†’ `{0;1;0;0;1;0;0;1}`
• `SUM(IF(ISNA(MATCH(\$C\$4:\$C\$11,\$D\$4:\$D\$11,0)),0,1)) `becomes
`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 February columns.

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

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

• `COUNTIF(\$C\$4:\$C\$11, C4)` â†’ returns the number of the value in cell C4 in the range \$C\$4:\$C\$11
Output â†’ 1
• `COUNTIF(\$D\$4:\$D\$11, C4)` â†’ returns the number of the value in cell C4 in the range \$D\$4:\$D\$11
Output â†’ 0
• `AND(COUNTIF(\$C\$4:\$C\$11,C4),COUNTIF(\$D\$4:\$D\$11,C4)) `becomes
`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 February columns which are 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 February column.

• `COUNTIF(\$C\$4:\$C\$11,\$D\$4:\$D\$11) `gives the matched number of cells between the ranges \$C\$4:\$C\$11 and \$D\$4:\$D\$11
Output â†’ `{0;0;1;1;0;0;0;1}`
• `SUMPRODUCT(COUNTIF(\$C\$4:\$C\$11,\$D\$4:\$D\$11)) `becomes
`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 February columns which is 3.

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

Steps:
âž¤ Type the following formula in cell D12

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

Here, \$C\$4:\$C\$11 is the range of the January column and \$D\$4:\$D\$11 is the range of the February column.

• `MATCH(\$C\$4:\$C\$11,\$D\$4:\$D\$11,0)` â†’ returns the relative position of the duplicate values in the range \$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}`
• `ISNUMBER(MATCH(\$C\$4:\$C\$11,\$D\$4:\$D\$11,0))` becomes
`ISNUMBER({#N/A;3;#N/A;#N/A;8;#N/A;#N/A;4})`
Output â†’ `{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}`
• `--(ISNUMBER(MATCH(\$C\$4:\$C\$11,\$D\$4:\$D\$11,0))) `becomes
`--({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE})` â†’ Double negation (â€“) will convert TRUE into 1 and FALSE into 0
Output â†’ `({0;1;0;0;1;0;0;1})`
• `SUMPRODUCT(--(ISNUMBER(MATCH(\$C\$4:\$C\$11,\$D\$4:\$D\$11,0)))) `becomes
`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 February columns.

### 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 January and February columns.
âž¤ 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.

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

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF