count no. of matching data in two excel

[rajagopal]

Hi,

I've 2 excels - Excel 1, column C has 500 student IDs and Excel 2, column Y has 300 student ids. Likewise i've the data in 6 sheets in both excel.

I want to know how many no. of student id as given in excel 1 is present in excel 2 in every sheets..

Please help

[mohanvijay]

use COUNTIF or SUMPRODUCT function

[shrivallabha]

You should use VLOOKUP to find this out.
Here's a reference for it:
http://office.microsoft.com/en-us/ex...005209335.aspx

[Bob Phillips]

SUMPRODUCT dear Henry, SUMPRODUCT

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A1:A20,Sheet2!A1:A20,0))))

[mohanvijay]

or like this

=SUMPRODUCT(--(COUNTIF(Sheet2!A1:A20,Sheet1!A1:A20)>0))