There are a lot of ways to compare two columns or lists in Excel. But using **the COUNTIF function** is the more convenient and fast way. We can use it individually or in a nested formula to do that. We’ll demonstrate 4 fast and useful ways in this tutorial to compare two columns using the **COUNTIF** function in Excel with sharp steps and clear images.

**Table of Contents**hide

**Download Practice Workbook**

You can download the free Excel workbook from here and practice independently.

**4 Suitable Ways to Compare Two Columns Using COUNTIF Function**

First of all, get introduced to our dataset that represents some salespersons’ selling region for two consecutive months. Now we’ll compare them to get the matches.

**1. Use Only COUNTIF Function to Compare Two Columns in Excel**

In our very first, method, we’ll apply only the **COUNTIF **function in a formula to return the match number for every value of **Column D** from **Column C**. It will show the total match number for every value.

**Steps:**

- Type the following formula in
**Cell E5**–

`=COUNTIF($C$5:$C$11,D5)`

- Then hit the
**Enter**button for the output.

- Next, use the
**Fill Handle**tool to copy the formula for the other cells of the column.

Soon after, we’ll get the match number for every value like the image below.

**Read More: ****How to Apply COUNTIF Between Two Cell Values in Excel**

**2. Combine IF and COUNTIF Functions to Compare Two Columns**

Now we’ll merge the **IF** and **COUNTIF **functions to compare two lists. Basically, we’ll use the previous formula with the **IF **function to return ‘Match’ and ‘No Match’ as the output for every value in **Column D**. It will help when we don’t need the match number, just need the match status.

**Steps:**

- Insert the following formula in
**Cell E5**–

`=IF(COUNTIF($C$5:$C$11,D5)=0,"No Match","Match")`

- Later, press the
**Enter**button to return the first output.

**Formula Breakdown:**

**COUNTIF($C$5:$C$11,D5)=0**

Firstly, the **COUNTIF **function will count the match number for every value of **Column D** from **Column C**. Then if it gets equal to zero then will return **TRUE **otherwise **FALSE**.

**IF(COUNTIF($C$5:$C$11,D5)=0,”No Match”,”Match”)**

Finally, the **IF **function will return ‘No Match’ for **TRUE **and ‘Match’ for **FALSE**.

- After that to get the other output, use the
**Fill Handle**tool.

Now see, the formula is returning the desired output.

**Read More: ****COUNTIF Function to Count Cells That Are Not Equal to Zero**

**Similar Readings**

**Excel COUNTIF with Greater Than and Less Than Criteria****Use Excel COUNTIF Function to Count Cells Greater Than 0****How to Calculate Frequency Using COUNTIF Function in Excel****Excel COUNTIF to Count Cell That Contains Text from Another Cell****How to Count If Cells Contains Text from List in Excel**

**3. Merge IF, COUNTIF and COUNTA Functions to Compare Two Columns**

To compare two columns in Excel, we can apply a formula that will return the match by comparing them in a row. That means it will search for a match only on that row. So, we’ll combine **the COUNTA function** with the **IF **and **COUNTIF **function to do that.

**Steps:**

- In
**Cell E5**, write the following formula-

`=IF(COUNTIF(C5:D5,D5)=COUNTA(C5:D5),"Match","Mismatch")`

- Then hit the
**Enter**button for the result.

**Formula Breakdown:**

**COUNTA(C5:D5)**

First, the **COUNTA **function will count cells from the range of the row.

**COUNTIF(C5:D5,D5)**

Then the **COUNTIF **function will count the match from the row based on the value of the second column.

**COUNTIF(C5:D5,D5)=COUNTA(C5:D5)**

After that, this logical test will happen to check whether it is **TRUE **or **FALSE**.

**IF(COUNTIF(C5:D5,D5)=COUNTA(C5:D5),”Match”,”Mismatch”)**

Finally, the **IF **function will return ‘Match’ for **TRUE **and ‘Mismatch’ for **FALSE**.

- Then apply the
**Fill Handle**icon for the output of the rest of the cells.

Here’s the final output of the comparison based on each row.

**Read More: ****How to Use Nested IF and COUNTIF Formula in Excel**

**4. Combine COUNTIF and SUMPRODUCT Functions to Compare Two Columns**

In our last method, we’ll compare by counting the total matches between two columns by using **the SUMPRODUCT function** with the **COUNTIF **function.

But it will need a modified dataset. In our previous dataset, we had duplicate values on each column. But for this method, there can be no duplicate values on a column, duplicates can remain only between columns. So we modified the dataset according to that requirement.

**Steps:**

- Insert the following formula in
**Cell D13**–

`=SUMPRODUCT(COUNTIF(C5:C11,D5:D11))`

- Hit the
**Enter**button and it will return 5 for our dataset.

**Formula Breakdown:**

**COUNTIF(C5:C11,D5:D11)**

The **COUNTIF **function will check if each value from **Column C** matches **Column A**. If matches then 1 will return, otherwise will return 0. It will return the output as an array- **{1;1;0;1;0;1;1}**

**SUMPRODUCT(COUNTIF(C5:C11,D5:D11))**

Finally, the **SUMPRODUCT **function will sum up all the values from the array and will return 5.

**Read More: ****COUNTIF Excel Example (22 Examples)**

**Conclusion**

That’s all for the article. I hope the above procedures will be good enough to compare two columns using the **COUNTIF** function. Feel free to ask any questions in the comment section and give me feedback.

## Related Articles

**[Fixed] COUNTIF Function with Wildcard Not Working in Excel****How to Use COUNTIF to Count Date Less Than Today in Excel****COUNTIF vs COUNTIFS in Excel (4 Examples)****How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)****VBA COUNTIF Function in Excel (6 Examples)****Count Blank Cells with Excel COUNTIF Function: 2 Examples****Apply COUNTIF Function in Multiple Ranges for Same Criteria**