How to Count Matches in Two Columns in Excel (5 Easy Ways)

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.

 Dataset for How does excel count matches in two 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))

Using SUMPRODUCT to Count Matches Alongside in Two Columns in Excel

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

Combining SUMPRODUCT & COUNTIF to Count All Matches in Two Columns in Excel

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

Result for using SUMPRODUCT & COUNTIF to Count All Matches in Two Columns in Excel

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.

Merging SUMPRODUCT, ISNUMBER & MATCH Functions Together to Count Matches in Excel

🔎 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


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.

Using COUNT & MATCH Functions to Count Matches in Any Two Columns

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

applying IF, and EXACT functions to count the matches alongside two columns in Excel

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

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.

Count Duplicates in Two Columns in Excel

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

Practice Section for how does excel count matches in two columns


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

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo