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

**Table of Contents**hide

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