Md. Sourov Hossain Mithun
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.
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
