SUMPRODUCT or another function???

[Paul Groth]

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

 

[Pete_UK]

You can put the doctor's name into Sheet2 by means of this formula:

=INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0))

and copy this down - suppose this is in column D, and the Examination
details are in column E.

Then your SP formula can be:

=SUMPRODUCT((Sheet2!D="Paul")*(Sheet2!E:E="X"))

But, you can only use full-column references if you are using Excel
2007. Prior to this the formula would have to be:

=SUMPRODUCT((Sheet2!D11000="Paul")*(Sheet2!E1:E1000="X"))

Hope this helps.

Pete

 

[Teethless mama]

=SUMPRODUCT(--(A1:A12="Paul"),--(ISNUMBER(MATCH(B1:B12,Sheet2!B1:B6,0))))

you can not use a whole column prior to XL-2007