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.

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

- Then we press
**Enter**.

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

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.

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

**📕 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**.

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.

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

**🔨 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.

## 📄 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

**5 Ways to Compare Three Columns Using Vlookup in Excel****2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel****4 Ways to Compare Three Columns in Excel and Return a Value****5 Ways to Compare 3 Columns for Matches in Excel**