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.

Data Set Example

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.


Enter this formula in cell A2


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

And drag down column A.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.