get a count from multiple lookups

[andy62]

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!

 

[Max]

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)*(Sheet2!$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)

 

[andy62]

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?

 

[bmstar]

I have a very similar problem.

I have attached the document below. On page two, in cell C:3, I want
to be able to be able to show a percentage. My goal is to look at each
record in the Game Play Sheet and compare colums C and J. I want the
percentage of times that column C has a 1T, 1S, 1M, or 1L and J has a
P* (any number or letter combo after it)...

Make any sense? I've tried to use "Countif" with "Counta" and
SumProduct....a couple IF statements....but to no avail. One of my
biggest challenges is that when I try to search multiple records I
can't use a wild card in the search pattern...and it's driving me
nuts.... (i.e. Countif (!GamePlaySheet:C11:C79="1*") ... any help?


+-------------------------------------------------------------------+
|Filename: Test Page.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5014 |
+-------------------------------------------------------------------+

 

[duane]

try using the find command, something like if(not(isna(find(1T))), ...

 

[Max]

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:
then I think we could use in a cell in Sheet3
=SUMPRODUCT(ISNUMBER(MATCH(Sheet2!$B$2:$B$20,Sheet1!$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]

.. I want the percentage of times that column C has a 1T, 1S, 1M, or 1L

and [col J] has a P* (any number or letter combo after it)...

Hazarding a guess, perhaps something like this ..

In sheet: 1st Down Dashboard,

Try in C3:
=SUMPRODUCT((ISNUMBER(SEARCH("P",'Game Play
Sheet'!J11:J100)))*(ISNUMBER(SEARCH(1,'Game Play
Sheet'!C11:C100))))/MIN(COUNTA('Game Play Sheet'!C11:C100),COUNTA('Game Play
Sheet'!J11:J100))
Format C3 as percentage (dp to taste)

The numerator SUMPRODUCT(...) returns the required counts satisfying
criteria in both cols C and J, while the denominator MIN(...) returns the
total "completed" plays in both cols C and P

(assuming "completed" plays are where both cols C and P contain inputs)

Replace SEARCH with FIND if you need it to be case sensitive (SEARCH is not
case sensitive). Adapt the ranges to suit ..

 

[bmstar]

Thanks Max. I appreciate your time and offering of your ability!

One more Question.....

Can I use wildcards in the search function or search for an {array} o
criteria?

Thanks again!

Be

 

[Max]

Thanks Max. I appreciate your time and offering of your ability!

You're welcome. I'll presume it worked for you?

One more Question.....
Can I use wildcards in the search function or search for an {array} of
criteria?

SEARCH in itself is implicitly wildcard <g>.

The suggested =SUMPRODUCT((ISNUMBER(SEARCH("P",'Game Play
Sheet'!J11:J100)))*( ... )) illustrates one possible usage of SEARCH in
SUMPRODUCT

=SUMPRODUCT(--ISNUMBER(SEARCH({"P","Q"},B2:B5)))
would be another "wildcard" example, this time with an array search,
ie search for either "P" or "Q" within B2:B5
(w/o case sensitivity)

 

[andy62]

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.

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:
then I think we could use in a cell in Sheet3:
=SUMPRODUCT(ISNUMBER(MATCH(Sheet2!$B$2:$B$20,Sheet1!$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]

You're welcome, Andy.

Try a fresh post if nobody drops by here
for the single cell formula that you seek.