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

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.


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.

How to Compare Two Columns Using COUNTIF Function


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.

Use Only COUNTIF Function to Compare Two Columns in Excel

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

Use Only COUNTIF Function to Compare Two Columns in Excel

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.

Combine IF and COUNTIF Functions to Compare Two Columns

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


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.

Merge IF, COUNTIF and COUNTA Functions to Compare Two Columns

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.

Combine COUNTIF and SUMPRODUCT Functions to Compare Two Columns

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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo