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