Thanks, Max, I may get some use from your formula for another statistic I
have to compile. And I learned something about how to use MATCH and ISNUMBER
in a SUMPRODUCT function. But since my count is of the role the people are
in rather than the people themselves, I would need to count all instances.

"Max" wrote:

"andy62" wrote:
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?

If it were not for your requirement:
If names are repeated in sheet1,
I do want to include the multiple occurences in my count.

then I think we could use in a cell in Sheet3:
=SUMPRODUCT(ISNUMBER(MATCH(Sheet2!$B$2:$B$20,Sheet 1!$A$2:$A$100,0))*(Sheet2!$H$2:$H$20<"R1"))

[The above returns the conditional count, but only for the unique items
within Sheet1!$A$2:$A$100. It'll return 2 for the sample data posted, instead
of the required 3]

Afraid I'm out of further suggestions for you. But do hang around awhile
for better insights from others to flow in.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---