# How to Compare Two Columns Using COUNTIF Function: 4 Methods

### Method 1 – Use Only COUNTIF Function to Compare Two Columns in Excel

Steps:

• Type the following formula in Cell E5
`=COUNTIF(\$C\$5:\$C\$11,D5)`
• Hit the Enter button for the output.

Get the match number for every value like the image below.

### Method 2 – Combine IF and COUNTIF Functions to Compare Two Columns

Steps:

• Insert the following formula in Cell E5
`=IF(COUNTIF(\$C\$5:\$C\$11,D5)=0,"No Match","Match")`
• Press the Enter button to return the first output.

Formula Breakdown:

• COUNTIF(\$C\$5:\$C\$11,D5)=0

The COUNTIF function will count the match number for every value of Column D from Column C. If it gets equal to zero then will return TRUE otherwise FALSE.

• IF(COUNTIF(\$C\$5:\$C\$11,D5)=0,”No Match”,”Match”)

The IF function will return ‘No Match’ for TRUE and ‘Match’ for FALSE.

• Get the other output, use the Fill Handle tool.

The formula returns the desired output.

### Method 3 – Merge IF, COUNTIF and COUNTA Functions to Compare Two Columns

Steps:

• In Cell E5, write the following formula-
`=IF(COUNTIF(C5:D5,D5)=COUNTA(C5:D5),"Match","Mismatch")`
• Hit the Enter button for the result.

Formula Breakdown:

• COUNTA(C5:D5)

The COUNTA function will count cells from the range of the row.

• COUNTIF(C5:D5,D5)

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)

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.

• Apply the Fill Handle icon for the output of the rest of the cells.

The final output of the comparison based on each row.

### Method 4 – Combine COUNTIF and SUMPRODUCT Functions to Compare Two Columns

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))

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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF