# How to Compare Two Columns Using COUNTIF Function (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

## How to Compare Two Columns Using COUNTIF Function: 4 Suitable Ways

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.  Soon after, we’ll get the match number for every value like the image below. ### 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. ### 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: COUNTIF Between Two Cell Values 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.

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  