Tanjima Hossain
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.
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.
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.
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))becomesSUM({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 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.
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))becomesAND(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 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 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))becomesSUMPRODUCT({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.
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.
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))becomesISNUMBER({#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))))becomesSUMPRODUCT({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.
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 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.
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.
