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)
--
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!

[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?

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

[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?

[duane]

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

[Max]

"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,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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bmstar" wrote:
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bmstar
> ------------------------------------------------------------------------
> bmstar's Profile: http://www.excelforum.com/member.php...o&userid=36264
> View this thread: http://www.excelforum.com/showthread...hreadid=560522
>
>

[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} of criteria?

Thanks again!

Ben

[Max]

"bmstar" wrote:
> 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

"bmstar" wrote:
> 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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.

"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,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
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

[Max]

You're welcome, Andy.

Try a fresh post if nobody drops by here
for the single cell formula that you seek.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andy62" wrote:
> 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.