get a single count after looking up a set of values

[Guest]

Please pardon the reposting, my original got a lot of discussion, masking the
lack of a final solution.

On sheet1 I have a column with multiple names. I need a function (in one
cell, on a separate sheet) that can look up all those names from column B of
sheet2, and then count the occurance of a value in column H of those same
rows in sheet2. Here's the example:

Sheet1 (note that some rows are blank, and some names may appear >1 time):
Helen Back
Cyndi Lou Who


Cyndi Lou Who


Sam Spade

Sheet2 (names only appear once):
col B col H
Cyndi Lou Who R2
Dan Danger R1
Dou Luva R3
Hal Brook R2
Helen Back R1
Joe Mama R2
Lou Duva R2
Mai Tai R2
Roberta Flack R3
Ron Popeil R2
Sam Spade R2
Will Not R2
Yoda n/a

So I need a single formula that can lookup sheet1's names (in this case,
four of them) in col B of sheet2, then give me a count of all corresponding
values "<>R1" in col H. If names are repeated in sheet1, I do need to
include the multiple occurences in my count.

Thanks in advance for any ideas!

 

[Roger Govier]

Hi Andy

On sheet 1 in cell B1
=IF(A1="","",SUMPRODUCT(--(Sheet2!$B$1:$B$1000=A1),--(Sheet2!$H1$:$H$1000<>"R1")))

Copy down column B as far as required

 

[Guest]

Thanks, but I am looking for a single formula in a single cell on a separate
sheet that can perform this function. This one, provided by Max, does the
trick except that it will only count a person once, whereas if someone shows
up three times in sheet1 I need them counted three times:

=SUMPRODUCT(ISNUMBER(MATCH(Sheet2!$B$2:$B$20,Sheet1!$A$2:$A$100,0))*(Sheet2!$H$2:$H$20<>"R1"))

Anyone have any ideas?

 

[Roger Govier]

Hi Andy

I can't figure out how to do it in a single cell, but working from the
same references as in Max's formula, you could modify my formula as
follows
=IF(B2="","",SUMPRODUCT(--(Sheet1!$A$2:$A$1000=B2),
--(Sheet1!$H$2:$H$1000<>"R1"),--(Sheet1!$A$2:$A$1000<>"")))

Enter this in cell C2 of sheet2 and copy down
Hide column C
in cell D2
=SUM(C:C) for your single formula