Counting matches in two columns in Excel refers to the process of calculating how many times the same value or specific criteria appear in both columns.
In this Excel tutorial, you will learn how to count matches in two columns in Excel with 3 cases using different methods. You will learn also the counting of case-sensitive matches.
Look at the following image. It shows the count of every duplicate from List 1 in List 2 – side-by-side and non-side-by-side matches. We highlighted the matches with different colors to get a better understanding.
Counting matches in Excel can be done based on several cases like when our matches are side by side; Not Side by side and based on individual total matches. To show those cases, we’ll need some functions like- SUM, SUMPRODUCT, IF, EXACT, COUNT, COUNTIF, MATCH functions.
Watch Video – Count Matches in Two Columns in Excel
Case 1. Count Matches in Two Columns (Match Side-by-Side)
Here, we’ll show how to count matches in two columns for side-by-side matches. That means we’ll only count matches if they remain in the same row. We’ll use the SUM, SUMPRODUCT, IF, EXACT functions for this case.
Have a look at our dataset, it has two lists of names. Some names are common in row-wise and some are not. Also, we included some case-sensitive names. For example, we kept a name in two forms- ‘Joe’ and ‘joe’.
1.1 Using SUM Function
Here, we’ll use the SUM function to count duplicates in two columns. In Excel 365 and Excel 2021, the SUM function can handle array operations without any special treatment like the SUMPRODUCT function. So we can easily apply it to count the matches between two columns row-wise.
- Insert the following formula in cell F5 and hit the Enter button in Excel 365 and Excel 2021. Press Ctrl+Shift+Enter in Excel 2019 or earlier versions-
=SUM(--(B6:B20 = C6:C20))The formula shows us that we have 6 row-wise duplicate matches.
- We also applied conditional formatting to highlight the row-wise duplicates with green color.
- Here’s the formula we used in conditional formatting-
=$B6=$C6
1.2 Using SUMPRODUCT Function
Now we’ll use the SUMPRODUCT function to count the duplicate matches in two columns row-wise. The advantage is, It can handle the array operation without Ctrl + Shift + Enter in the earlier versions too.
- Apply the following formula in cell F6 and hit the Enter button–
=SUMPRODUCT(--(B6:B20 = C6:C20))It returned the same output as the previous method.
1.3 Combining Excel EXACT and IF Functions for Case-Sensitive Match
Here, we’ll use the IF, COUNTIF, and EXACT functions to consider case sensitivity in counting matches in two columns. The EXACT function is used to check multiple text strings or values whether they are exactly equal or not. So basically, we’ll count only those matches in rows that match cases of the letters too.
To show that operation, we needed a helper column, so we used column D as the helper column.
- Use the following formula in cell D6 and press the Enter button-
=IF(EXACT(B6,C6),"Common","Unique")- Then apply the Fill Handle tool to copy the formula in the other cells of the column.
This formula will return ‘Unique’ if it doesn’t get exact same values with the same cases, and return ‘Common’ if it gets exact same values with the same cases.
- Finally, use the following formula in cell G6 to count the matching values-
=COUNTIF(D6:D20,"Common")
Here, ‘Tom’, ‘Joe’, and ‘Alfred’ didn’t match with cases. So the formula didn’t count them as matches.
Read More: How to Compare Two Columns in Excel for Match
Case 2. Count Matches in Two Columns (Not Side by Side)
Now we’ll count the matches or duplicates in two columns not in a side-by-side position but rather from any position of both columns. For that, we’ll use the COUNT, COUNTIF, ISNUMBER, MATCH, and SUMPRODUCT functions.
2.1 Combining Excel SUMPRODUCT & COUNTIF Functions
In this method, we’ll combine the SUMPRODUCT & COUNTIF functions to count the duplicates of column B in column C.
- Insert the following formula in cell F6 and press the Enter button-
=SUMPRODUCT(COUNTIF(B6:B20,C6:C20))The formula has returned the total count 11 because there are 11 duplicates of the name of column B in column C including two names (Max and Austin) that exist 2 times in column C.
- We used individual conditional formatting to highlight duplicates of each item with different colors.
2.2 Merging Excel SUMPRODUCT, ISNUMBER & MATCH Functions
Here, we’ll apply the SUMPRODUCT, ISNUMBER & MATCH functions to count matches in two columns. In our previous method, we used the COUNTIF function with the SUMPRODUCT function. If we use the ISNUMBER & MATCH functions instead of the COUNTIF function, then it will result in the same.
The MATCH function returns the relative position of matched items, based on this position ISNUMBER and SUMPRODUCT functions count them from the array.
- Insert the below formula in cell F6 to count the matches of column B in column C–
=SUMPRODUCT(--(ISNUMBER(MATCH(C6:C20,B6:B20,0))))- After hitting the Enter button we’ll get the total match number.
2.3 Joining COUNT & MATCH Functions
Now, we’ll use the combination of COUNT & MATCH functions for counting matches or duplicates of column B in column C.
- In cell F6, apply the following formula and press the Enter button to get the total matched count-
=COUNT(MATCH(C6:C20,B6:B20,0))
Read More: How to Match Multiple Columns in Excel
Case 3. Count Individual Total Match
In our last case, we’ll use the COUNTIF function to count the number of individual matches of column B in column C.
- To find the total matches of ‘Max’ in column C, insert the following formula in cell F6–
=COUNTIF($C$6:$C$20,E6)
- Here’s the conditional formatting that we used to highlight the matches.
- For more dynamic selection, we created a drop-down list for the names of column B in cell E6. whenever you select a name, instantly it will show the count and highlight the duplicates.
Read More: How to Compare 3 Columns for Matches in Excel
Download Practice Workbook
You can download our free practice workbook to practice the methods.
This article has shown 3 practical cases with the use of different useful functions like SUM, SUMPRODUCT, COUNT, COUNTIF, MATCH functions to count matches in two columns in Excel. We hope it will help you to count different kinds of matches according to your needs. We included conditional formatting and data validation to give e better dynamic approach. Thanks for reading and please leave comments in the comment section for your queries and feedback.
Related Articles
- How to Compare Three Columns and Return a Value in Excel
- How to Compare Three Columns Using VLOOKUP in Excel
- How to Compare Two Columns Using VLOOKUP Function in Excel
- VLOOKUP Formula to Compare Two Columns in Different Excel Sheets
- How to Compare 4 Columns in Excel
- How to Compare 4 Columns in Excel VLOOKUP
