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.
- 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 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.
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.
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.
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
- How to Use COUNTIF Function in Excel Greater Than Percentage
- Excel COUNTIF to Count Cells Greater Than 1
- How to Use COUNTIF for Non Contiguous Range in Excel
- How to Use COUNTIF Function to Calculate Percentage in Excel
- How to Use COUNTIF Function with Array Criteria in Excel
- How to Calculate Frequency Using COUNTIF Function in Excel
- Excel COUNTIF Function with Conditional Formatting
