Thanks, but I may not have been clear that I need a single formula, located
in a cell over on a sheet3, to provide me with the total. I'd prefer not to
add an extra column to sheet1 to give me a count for each row. Is it
possible to have a single formula that can directly count all those lookups?

"Max" wrote:

One way ..

Assuming Sheet2's data in cols B and H is within row2 - row20

In Sheet1, assume names are running in A2 down

Put in say, B2:
=IF(A2="","",SUMPRODUCT((Sheet2!$B$2:$B$20=A2)*(Sh eet2!$H$2:$H$20<"R1")))
Copy down

Adapt the ranges to suit, but note that we can't use entire col references
in SUMPRODUCT (eg: B:B, H:H)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andy62" wrote:
On sheet1 I have a column with multiple names. I need a function that can
look up all those names from column B of sheet2, and then count the occurance
of a value in column H of 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 formula that can do lookup sheet1's names in col B of sheet2,
then give me a count of all values "<R1" in col H. If names are repeated in
sheet1, I do want to include the multiple occurences in my count.

Thanks in advance. I hope this is easy for you while at the same time not
something I could have come up with on my own!