# Count the numbers of matches between a row and a column in Excel

I have a data set in which I need to count the number of matches between any ID numbers in a column and equal ID numbers in a row. I have attached a data set example image and hopefully that will makes things a bit more clear.

I was looking to get help with a formula that would compute the values in Column `A` (Row 2 and below). The other columns are the pre-existing input data. Column `B` is special; columns `C`, `D`, `E`, ..., are all the same kind of data. I want cell `An` to count the number of matches between Column `B` and Row `n`, to the right of Column `B`. For example, `A2` is 1 because `B3` (Column `B`) equals `D2` (Row 2), and `A4` = 3 because Column `B` and Row 4 have three matches: `B6`=`C4`, `B3`=`D4`, and `B2`=`E4`.

I have tried a couple of different things like `COUNTIF` and `COUNTIFS`, but those don't seem to get at my problem.

## 1 Answer

Enter this formula in cell A2

``````=COUNT(IF(C2:K2=\$B\$2:\$B\$6,1))
``````

as an Array formula - Press `Ctrl + Shift + Enter` not just `Enter` after typing the formula.

And drag down column A.