Nehad Ulfat
Microsoft Excel provides a wide range of fruitful techniques to count matches of data in two columns under many different criteria. In this article, we’ll try to illustrate how you can count matches in two columns in Excel with proper instructions along with explanations of the functions. For conducting the session, we’re going to use Microsoft 365 version.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article. Moreover, you can change & customize input data and see the results at once.
5 Easy Ways to Count Matches in Two Columns in Excel
Today we will see how to count Matches in two columns in Excel. Furthermore, we will demonstrate how to do that in 5 suitable methods. Now, let’s dive into the session.
But, before diving into the big picture, let’s get to know about today’s Excel sheet first.
The Excel sheet is about two lists of names. There are two columns, 1st Name List, and 2nd Name List. Now, we will find out the Matches in those columns.
1. Using SUMPRODUCT to Count Matches Alongside in Two Columns
We can use only one function which is the SUMPRODUCT function to count the matches alongside in two columns.
Here, we have two columns of names where we have to see how many names are matched alongside both columns.
Steps:
- Firstly, in the cell E9, type-
=SUMPRODUCT(--(B5:B15 = C5:C15))
- Now, press ENTER.
As a result, you’ll see 4 names (Max, Tom, Cooper & Austin) are matched alongside. Furthermore, we have bold those matched names for your better understanding.
🔎 Short Explanation of the Formula:
- Here, B5:B15 = C5:C15 will look for the matches & return the value as TRUE or FALSE.
- Double-Hyphen or Double-Unary(- -) has been used to convert the logical values (TRUE & FALSE) into numbers (1 or 0 respectively).
- The SUMPRODUCT function will count & sum all values (1’s & 0’s) found in the whole array.
Read More: How to Compare Two Columns in Excel for Match (8 ways)
2. Combining SUMPRODUCT & COUNTIF to Count All Matches in Two Columns
Now if we want to count all matches between two columns or ranges of names then we can combine SUMPRODUCT & COUNTIF functions.
Steps:
- Firstly, in the cell E9, type-
=SUMPRODUCT(COUNTIF(B5:B15,C5:C15))- Secondly, press ENTER and you’ll see 5 matches (Max, Malcolm, Sam, Austin, Tom) here.
🔎 Short Explanation of Formula:
- Here, in this formula, COUNTIF finds each name of Column C from the range of names in Column B, and this function will return the result in numerical value (Number of times found as matches) for each data in Column C.
- Then, the SUMPRODUCT function will sum up all the matches counted by COUNTIF function.
Furthermore, we’ve shaded the matches with colors.
Moreover, you can customize the matches with conditional formatting by following the steps below-
- Firstly, select the whole array of names (B5:C15).
- Secondly, from the Home tab >> in the Styles group of commands, you’ll find a drop-down named Conditional Formatting.
- Thirdly, from the sidebar drop-down Highlight Cells Rules >> choose Duplicate Values… command.
At this time, a box named Duplicate Values will appear.
- Now, choose Duplicate in the Format cells that contain: box.
- Then, from the values with drop-down options, select your preferred color and press OK.
So, here’s the instance of conditional formatting below with customized colors of shades overall matched names.
Read More: Macro to Compare Two Columns in Excel and Highlight Differences
3. Merging SUMPRODUCT, ISNUMBER & MATCH Functions to Count Matches
Here’s another method we can try to find matches in two columns and count them all. Additionally, we’re going to use SUMPRODUCT, ISNUMBER, and MATCH functions.
Steps:
- Firstly, in the cell E9, type-
=SUMPRODUCT(--(ISNUMBER(MATCH(C5:C15,B5:B15,0))))- Secondly, press ENTER and you’ll have the same result as found in the previous method.
🔎 Short Explanation of the Formula:
- Here, the MATCH function will look up the names from Column C in Column B and return the resultant value as the position number of each data.
- Then, the ISNUMBER is a logical function that determines if data in a cell is a number (TRUE) or not (FALSE).
- After that, Double-Unary(–) converts these logical values into numbers (1 and 0) as mentioned in the first method.
- Lastly, the SUMPRODUCT will do the rest by evaluating all these numbers found.
Moreover, you can have more detailed ideas on ISNUMBER & MATCH functions by clicking on the terms, mentioned at the beginning of this method.
Furthermore, we’ve shaded the matches with colors.
Read More: Match Two Columns and Output a Third in Excel (3 Quick Methods)
Similar Readings
- How to Compare Text in Two Columns in Excel
- Excel Compare Two Lists and Return Differences (4 Ways)
- How to Compare Text of Two Cells in Excel (10 Methods)
- Compare Two Columns or Lists in Excel (4 Suitable Ways)
- How to Compare Two Columns for Finding Differences in Excel
4. Using COUNT & MATCH Functions to Count Matches in Any Two Columns
Here is another method where we’ll combine COUNT and MATCH functions together to count all the matches in two columns.
Steps:
- Firstly, in the cell E9, type-
=COUNT(MATCH(C5:C15,B5:B15,0))- Secondly, if you’re using Excel 365 then press ENTER, otherwise, press CTRL+SHIFT+ENTER in all other versions of Excel & you’re done.
Lastly, you’ll see 5 matches (Max, Malcolm, Sam, Austin, Tom) here.
🔎 Short Explanation of the Formula:
- Like before, the MATCH function will look for the positions in Column B for all data from Column C.
- Then, the COUNT function along with enclosed braces will count all these position numbers that are found through the MATCH function.
Similarly, we’ve shaded the matches with colors for your better understanding.
Read More: Excel Formula to Compare and Return Value from Two Columns
5. Use of EXACT and IF Functions
You can apply IF, and EXACT functions to count the matches alongside two columns in Excel. Let’s do something different. Here, we will take an extra column named Status. Furthermore, in this method, we will find the alongside matches first, then we will count the number of matches. The steps are given below.
Steps:
- Firstly, use the following formula in the D5 cell.
=IF(EXACT(B5,C5),"Common","Unique")- Secondly, press ENTER.
🔎 Short Explanation of the Formula:
- Here, the EXACT function will check whether the two cell values are the same or not. If the values match then it will return TRUE otherwise FALSE.
- Then, the IF function will consider the EXACT function as a logical test. So, when the EXACT function returns TRUE then the IF function will give Common as Status. Otherwise, the IF function will give Unique as the Status.
- Now, you can write the formula for the rest of the rows or simply use Excel AutoFill Feature.
Subsequently, you will find all alongside matches.
At this time, we will find the total number of matches using the COUNTIF function.
- Now, use another formula in the C17 cell.
=COUNTIF(D5:D15,"Common")Here, the COUNTIF function will count those cells which contain Common as the Status.
- Then, press ENTER.
Lastly, you will see how many matches there.
Furthermore, we have bold those matched names (Max, Tom, Cooper & Austin) for your better understanding.
Read More: How to Compare Two Columns and Return Common Values in Excel
Count Duplicates in Two Columns in Excel
In this section, we will see the counting of duplicates in two columns in Excel. Here, we will use only the COUNTIF, and IF functions. Now, let’s talk about the steps.
- Firstly, you must select a new cell D5 where you want to keep the number of duplicates.
- Secondly, let’s write the formula given below in the D5 cell.
=COUNTIF($B$5:$C$15,B5)Here, $B$5:$C$15 is the range where we want to count duplicates, and B5 is the specific cell that we want to count (criteria).
- Thirdly, press ENTER.
- Now, you can use the Excel AutoFill Feature to get the values of the rest of the cells.
If you look closely at the output, you’ll find that the values of duplicates are more than 2. On the other hand, the values of unique records are 1. So, we can assign the logical IF function to return Duplicates instead of getting numerical values.
- Now, use the corresponding formula in the E5 cell.
=IF($D$5:$D$15>1,"Common","Unique")Here, If the value is greater than 1, the above formula will return Common. Else it’ll return Unique.
- Then, press ENTER.
- Again, use another formula in the C17 cell.
=COUNTIF(E5#,"Common")Here, the COUNTIF function will count those cells which contain Common as the Status.
- Then, press ENTER.
Lastly, you will see how many duplicates there.
Read More: Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)
Practice Section
Now, you can practice the explained method by yourself.
Concluding Words
So, these are all basic & easy ways about how to count the matches between two columns in Excel. If you find this article useful, you can comment or give us feedback. You can also look for our other interesting & informative articles on Excel functions for regular use on this website ExcelDemy.
Further Readings
- How to Compare Multiple Columns Using VLOOKUP in Excel (5 Methods)
- Compare Two Columns in Excel for Missing Values (4 ways)
- How to Compare 4 Columns in Excel (6 Methods)
- Excel formula to compare two columns and return a value (5 examples)
- How to Match Two Columns and Return a Third in Excel
- Match Multiple Columns in Excel (Easiest 5 ways)
