How to Count Matches in Two Columns in Excel (3 Practical Cases)

Get FREE Advanced Excel Exercises with Solutions!

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.

1- How to Count Matches in Two Columns in Excel
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’.

2- Dataset for counting matches in two columns in Excel


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.

3- Using the SUM function to count matches between two columns row-wise

  • 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

4- Applying conditional formatting to highlight matches between two columns row-wise


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.

5- Applying the SUMPRODUCT function to count matches between two columns row-wise


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.

6- Inserting a helper column to count matches in two columns row-wise and case-wise

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

7- Joining the IF and EXACT functions to identify matches in two columns row-wise and case-wise

  • Finally, use the following formula in cell G6 to count the matching values-
=COUNTIF(D6:D20,"Common")

8- Using the COUNTIF function to count matches in two columns row-wise and case-wise

Here, ‘Tom’, ‘Joe’, and ‘Alfred’ didn’t match with cases. So the formula didn’t count them as matches.


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.

9- Combining SUMPRODUCT & COUNTIF functions to count matches in two columns from any position

  • We used individual conditional formatting to highlight duplicates of each item with different colors.

10- Applying conditional formatting to highlight matches of each item with different color


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.

11- Combining SUMPRODUCT, ISNUMBER & MATCH functions to count matches in two columns from any position


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

12- Combining COUNT & MATCH functions to count matches in two columns from any position

 


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.

13- Modified dataset to count the number of individual matches

  • To find the total matches of ‘Max’ in column C, insert the following formula in cell F6–
=COUNTIF($C$6:$C$20,E6)

14- Applying the COUNTIF function to count the number of individual matches

  • Here’s the conditional formatting that we used to highlight the matches.

15- Using conditional formatting to highlight the individual matches of each item

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

16- Using a drop-down list to select an item from the name list


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.


<< Go Back to Columns | Compare | 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.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo