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.

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

Excel count duplicate values in multiple 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.

Excel count duplicate values in multiple columns

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.

COUNTIF Function

➤ Press ENTER and drag down the Fill Handle tool.

COUNTIF Function
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.

Excel count duplicate values in multiple columns

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.

COUNTIF Function

After pressing ENTER, you will get 3 as the number of the duplicate values between the January and February columns.

Excel count duplicate values in multiple 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 February columns.

Excel count duplicate values in multiple 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

SUM, IF, ISNA, MATCH Function

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

SUM, IF, ISNA, MATCH Function

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

Excel count duplicate values in multiple 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

AND, COUNTIF Function

➤ Press ENTER and drag down the Fill Handle tool.

Excel count duplicate values in multiple columns

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.

AND, COUNTIF Function

➤ Type the following formula in cell E12

=COUNTIF(E4:E11,TRUE)

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

AND, COUNTIF Function

➤ Press ENTER.
Finally, you will be able to count the duplicate values in the January and February columns which are 3.

Excel count duplicate values in multiple columns

Read More: How to Count Duplicates in Two Columns in Excel (8 Methods)


Similar Readings:


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.

Excel count duplicate values in multiple columns

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

SUMPRODUCT, COUNTIF Function

➤ Press ENTER.
Afterward, you will get the number of duplicate values in the January and February columns which is 3.

SUMPRODUCT, COUNTIF Function

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

Excel count duplicate values in multiple 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

SUMPRODUCT, ISNUMBER, MATCH Function

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

SUMPRODUCT, ISNUMBER, MATCH Function

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.

Excel count duplicate values in multiple columns

Steps:
➤ Select the January and February columns.
➤ Go to Home Tab >> Styles Group >> Conditional Formatting Dropdown >> Highlight Cells Rules Option >> Duplicate Values Option.

Conditional Formatting

Then, the Duplicate Values wizard will appear.
➤ Select the Custom Format option in the second box.

Conditional Formatting

Now, the Format Cells dialog box will open up.
➤ Select Fill Option, choose the Yellow Color, and press OK.

Excel count duplicate values in multiple columns

Again, press OK in the Duplicate Values dialog box.

Conditional Formatting

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

Conditional Formatting

➤ Select any of the columns between January and February.
➤ Go to Home Tab >> Editing Group >> Find & Select Dropdown >> Find Option.

Excel count duplicate values in multiple columns

After that, the Find and Replace Dialog Box will pop up.
➤Select the Format Option.

Conditional Formatting

Then, the Find Format Dialog Box will appear.
➤ Select Fill Option, choose the Yellow Color, and press OK.

Conditional Formatting

Afterward, the following Preview section will appear.
➤ Click the Find All option.

Conditional Formatting

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

Excel count duplicate values in multiple columns

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.

Practice


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

Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo