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.

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

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 Use COUNTIF to Count Date Less Than Today 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


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

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: How to Use Excel COUNTIF Between Time Range


Download Practice Workbook

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.


Related Articles


<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo