Hi All,

I am at my wits ends trying to do some excel magic so after days
trying to figure this out myself I have turned to here in
desperation.. I am hoping someone can tell me if I am on the right
track or point me in the right direction.

What I have is a large amount of raw data split across a number of
worksheets.  It clinical data so I can't post here, but essentially
the first sheet contains a doctors name and a request number plus
other misc data.. The second , third, fourth etc worksheets contains
specific information about a type of examination.. These subsequent
worksheets do not contain the doctors name, but only contain the
request number and patient related details..

I want to count how many times a a doctor has performed examination X
and examination Y and so on by matching the request numbers in the
first sheet to the request numbers in the second,third, fourth etc
worksheet..  I figured this would be possible by using sumproduct but
have had absolutely no luck getting this to work..

So as an example I have the following data in sheet 1 and sheet 2

Sheet 1
A              B
Paul    1
Fred    2
Joe    3
Michael    4
Paul    5
Paul    6
Paul    7
Paul    8
Fred    9
Joe    10
Michael    11
Joe    12

Sheet 2
Patient data    3
Patient data    5
Patient data    6
Patient data    7
Patient data    10
Patient data    12

I have used the sumproduct function as follows
=SUMPRODUCT((A:A="Paul")*(B:B=Sheet2!B:B)) and I get 0 as an answer
which I don't understand.. In the first worksheet i figured it would
check that the first column had "Paul" in it and then check whether
entries in column b matched between both sheets...

Is sumproduct the right function to use considering the size/number of
rows in each sheet can differ dramatically?  or is there another
function that give me the answers I want?

Thanks in advance

Paul