get a single count after looking up a set of values

[andy62]

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

--
Regards

Roger Govier


"andy62" <andy62@discussions.microsoft.com> wrote in message
news:9E8EED16-2C6A-4EE9-A190-6E78AA53A3B1@microsoft.com...
> 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!

[andy62]

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

> 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
>
> --
> Regards
>
> Roger Govier
>
>
> "andy62" <andy62@discussions.microsoft.com> wrote in message
> news:9E8EED16-2C6A-4EE9-A190-6E78AA53A3B1@microsoft.com...
> > 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

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

--
Regards

Roger Govier


"andy62" <andy62@discussions.microsoft.com> wrote in message
news:4DF07D7F-E6A3-425B-9675-E05CF15DE304@microsoft.com...
> 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" wrote:
>
>> 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
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "andy62" <andy62@discussions.microsoft.com> wrote in message
>> news:9E8EED16-2C6A-4EE9-A190-6E78AA53A3B1@microsoft.com...
>> > 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!
>>
>>
>>