# Using COUNTIF function to compare two columns in Excel

I have an excel document with columns A and B. Row A has 2595 values. Row B has 411 values.

I’m trying to figure out how I can compare these two columns by marking column C with the number of times we find a value(s) from column A.

For Example

COLUMN A COLUMN B COLUMN C 1 1 1 (because we have one value of 1 from column A) 2 2 1 (because we have one value of 2 from column A) 3 3 2 (because we have 2 threes from column A) 3 4 4 5 5 6 2 (because there are two 6's in column A) 6 7 0 (because there are no 7's in column A) 6

We can use the **COUNTIF** function in Excel to do this.

Column A Column B Column C Answer 1 1 =COUNTIF($A$2:$A$9,B2) [1] 2 2 =COUNTIF($A$2:$A$9,B3) [1] 3 3 =COUNTIF($A$2:$A$9,B4) [2] 3 5 =COUNTIF($A$2:$A$9,B5) [1] 4 6 =COUNTIF($A$2:$A$9,B6) [2] 5 7 =COUNTIF($A$2:$A$9,B7) [0] 6 6