Matching two columns from separate sheets

I have a column in a worksheet named ID which contains unique text values like abc14, dad88, etc.

I have a similar column in the second sheet of the same file, and some of the IDs in each of these sheet are similar, but some only happen on one of the two sheets. I want a formula which can match these columns in count the number of IDs which are present in both of them. I want this number to be shown in a cell in the third sheet.

If it was only about matching one cell against a column, I could use something similar to this: =IF(ISNUMBER(MATCH(B1,$A$1:$A$7,0)),1,0)

However, I want the cell to show the total count of matching one column against another column.

Please help me.

Originally Posted by Merik

I want a formula which can match these columns in count the number of IDs which are present in both of them. I want this number to be shown in a cell in the third sheet.

If you want a single cell calculation in the third sheet without need for helps on sheet "ID" then:

Please Login or Register  to view this content.

modify ranges to suit but note that with SUMPRODUCT the ranges used should be kept as lean as possible.

Conduct an AND test

Please Login or Register  to view this content.

The above will return the Count of items in Sheet1!$B$1:$B$100 that appear in both lists
(where Sheet1 contains unique items per your original post)

Modify ranges to suit.

For more info. on SUMPRODUCT and coercion see the link in my sig. to Bob Phillips' white paper.