Excel Count Matches in Two Columns (4 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, I’ll try to illustrate how you can count matches in two columns in Excel with proper instructions along with explanations of the functions.

Download Practice Workbook

You can download our Excel workbook that we’ve used to prepare this article. You can change & customize input data and see the results at once.

4 Easy Ways to Count Matches in Two Columns in Excel

1. Using SUMPRODUCT to Count Matches Alongside in Two Columns

If we have two columns of data where data in one column need to be inspected if they’re or how many of them are present in another column then SUMPRODUCT function offers the best solutions with a number of criteria.

Here, we have two columns of names where we have to see how many names are matched alongside in both columns.

Steps:

➤ In Cell D10, type-

➤ Press Enter.

You’ll see 2 names(Max & Tom) are matched alongside.

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

⇒ SUMPRODUCT function will count & sum all values(1’s & 0’s) found in the whole array.

If you want to know in detail how SUMPRODUCT function works then you can have a look at this link. 

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:

➤ In Cell D10, type-

➤ Press Enter and you’ll see 5 matches(Max, Malcolm, Sam, Austin, Tom) here.

🔎 Short Explanation of the Function:

⇒ In this function, COUNTIF finds each name from Column C in 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.

⇒ SUMPRODUCT function will sum up all the matches counted by COUNTIF functions.

I’ve shaded the matches with colors and you can customize them too with conditional formatting by following the steps below-

➤ Select the whole array of names(B4:C15).

➤ Under the Home tab and in the Style group of commands, you’ll find a drop-down named Conditional Formatting.

➤ From the sidebar drop-down Highlight Cells Rules, choose Duplicate Values… command. A box will appear.

➤ Now 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 color of shades over all matched names.

3. Merging SUMPRODUCT, ISNUMBER & MATCH Functions Together to Count Matches

Here’s another method we can try to find matches in two columns and count them all.

Steps:

➤ In Cell D10, type-

➤ Press Enter and you’ll have the same result as found in the previous method.

🔎 Short Explanation of the Function:

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

⇒ ISNUMBER is a logical function that determines if a data in a cell is number(TRUE) or not(FALSE).

⇒ Double-Unary(–) converts these logical values into numbers(1 and 0) as mentioned in the first method.

⇒ SUMPRODUCT will do the rest by evaluating all these numbers found.

You can have more and detailed ideas on ISNUMBER & MATCH functions by clicking on the terms.

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

And here’s the last method we’ll combine COUNT & MATCH functions together.

Steps:

➤ In Cell D10, type-

➤ If you’re using Excel365 then press Enter, otherwise, press CTRL+Shift+Enter in all other versions of Excel & you’re done.

🔎 Short Explanation of the Function:

⇒ Like before, MATCH function will look for the positions in Column B for all data from Column C.

⇒ COUNT function along with enclosed braces will count all these position numbers that are found through MATCH function.

Concluding Words

So, these are all basic & easy ways you can find & 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 uses on this website.

Further Readings

• Excel Compare Text in Two Columns (7 Fruitful Ways)
• Excel Compare Two Cells Text (9 Examples)
• Match Two Columns in Excel and Return a Third (3 Ways)
• Excel formula to compare two columns and return a value (5 examples)
• VLOOKUP Formula to Compare Two Columns in Different Sheets!
• How to Compare Two Columns or Lists in Excel