How to Count Matches in Two Columns in Excel

Most of us have probably had to compare two columns of data and count matches or how many times they have the same data in Excel. For example, let’s say you have two columns of names, and some of the names are in both the first and second columns. You want to count all the names that match, no matter where they are in the two columns. This tutorial will show you how to do this in Excel using formulas.


📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.


Count Matches in Two Columns in Excel with These 4 Easy Methods

Today, we’ll learn how to count the number of matches in two Excel columns. Also, we’ll show you 4 easy ways to do that. Let’s get started with the session.

There are two lists of names on the Excel sheet. One for the cricket team and another for the football team. Both teams are chosen from class 10 for the intra-school sports competition.  There are two columns: the Football Players List and the Cricket Players List. Now, we’ll look at the matches in those columns.

excel count matches in two columns


Method 1

Using SUMPRODUCT Function

To count the matches alongside two columns, we can only use the SUMPRODUCT function. The SUMPRODUCT function counts and adds up all the ones and zeros in an array.

Here, there are two columns of names, and we need to find out how many names are identical across both columns.

⬇️⬇️ STEPS ⬇️⬇️

  • First of all, in the cell E10, we type-

=SUMPRODUCT(- -(B6:B17 = C6:C17))

Count Match Alongside

  • Then we press Enter.

excel count matches in two columns by Count Matches Alongside

Here we see, 2 names are matched alongside. Also, the names that match are in bold to help you understand better.

🔨  Formula Breakdown

👉  Here, B5:B16 = C5:C16 will look for matches and return either TRUE or FALSE, depending on what it finds.

👉  Double-Hyphen or Double-Unary (- -) has been used to turn logical values (TRUE and FALSE) into numbers (1 or 0 respectively).

👉  The SUMPRODUCT function will count and add up all the ones and zeros in an array.

📕 Read More: 4 Ways to Compare Two Columns in Excel for Missing Values


Method 2

Combining SUMPRODUCT and COUNTIF Functions

We use the SUMPRODUCT and COUNTIF functions together to count all of the matches between two columns. This formula uses the COUNTIF function to find each name of one Column in another Column. For each piece of data in the first Column, the function will give back a number that shows how many times it found a match.

The SUMPRODUCT function counts and adds up all the ones and zeros in an array.

⬇️⬇️ STEPS ⬇️⬇️

  • First of all, in the cell E10, we type-

=SUMPRODUCT(COUNTIF(B6:B17,C6:C17))

  • Then we press Enter.

excel count matches in two columns by using SUMPRODUCT

Here we see, 7 names are matched.

🔨  Formula Breakdown

👉  This formula uses COUNTIF to find each name in Column C from the list of names in Column B. The function will return the result as a numerical value (the number of times detected as matches) for each bit of data in Column C.

👉  After that, the COUNTIF function’s total matches will be added up by the SUMPRODUCT function.

Additionally, you can modify the matches using conditional formatting by doing the following:

⬇️⬇️ STEPS ⬇️⬇️

  • First, choose all the names (B6:C17).
  • Second, you may use a drop-down menu called Conditional Formatting from the Home tab >> in the Styles group of commands.
  • Finally, from the sidebar drop-down Highlight Cells Rules, then choose the Duplicate Values… command.

Count Match Cutomized

  • Select Duplicate from the Format cells that contain: box now.
  • Select your desired color from the values with the drop-down box, and then click OK.

So, the following is an example of conditional formatting with customized colors and names that match overall.

excel count matches in two columns

📕 Read More: Excel Formula: Compare Two Columns and Return a Value


Method 3

Applying COUNT and MATCH Functions

With the COUNT and MATCH functions together, you can also find out the matches between two columns. The MATCH function finds all the position numbers, and the COUNT function adds up all of them.

⬇️⬇️ STEPS ⬇️⬇️

  • First of all, in the cell E10, let’s type-

=COUNT(MATCH(B6:B17,C6:C17,0))

  • Second, press Enter if you’re using Excel 365. For all other versions of Excel, press Ctrl+Shift+Enter.

excel count matches in two columns by applying COUNT and MATCH Functions

Here you will find seven matches: Tom, Jack, Austin, Sam, David, Sifat, and Han.

🔨  Formula Breakdown

👉  The MATCH function will look for the positions in Column B for all the data in Column C, just as it did before.

👉  Then, the MATCH function will find all the position numbers, and the COUNT function will count them all.

Also, we have shaded the names that match with color to help you understand better.

Applying COUNT and MATCH Functions


Method 4

Merging SUMPRODUCT, ISNUMBER, And MATCH Functions

Using the SUMPRODUCT, ISNUMBER, and MATCH functions in Excel, you may attempt to find the matches in two columns and count them. The SUMPRODUCT function counts and adds up all the ones and zeros in an array. The ISNUMBER function checks to see if the data in a cell is a number (TRUE) or not (False). The MATCH function finds all the position numbers

⬇️⬇️ STEPS ⬇️⬇️

  • First of all, in the cell E10, let’s type-

=SUMPRODUCT(- -(ISNUMBER(MATCH(C5:C16,B5:B16,0))))

  • Then we press Enter.

excel count matches in two columns by merging SUMPRODUCT, ISNUMBER, And MATCH Functions

🔨  Formula Breakdown

👉  Here, the MATCH function will look for the names from Column B in Column C and return the result as the position number of each data.

👉  The ISNUMBER function then checks to see if the data in a cell is a number (TRUE) or not (FALSE).

👉  Then, as in the first method, Double-Unary(–) changes these logical values into numbers (1 and 0).

👉  Lastly, the SUMPRODUCT will take care of the rest by adding up all the numbers found.

Again, we have shaded the names that match with color to help you understand better.

excel count matches in two columns by merging


📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.


📝  Takeaway from This Article

📌  Reader will be able to count matches in two columns with various methods in Excel.

📌  One will be able to identify the quickest method to count matches in two columns in Excel.


Conclusion

That concludes today’s session. These are the methods for counting matches in two columns in Excel. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.


Related Articles

(Visited 152 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo