Counting Team Deals
 

Is there a formula to highlight and/or count the number of team deals.
Ideally, I'd like to count the number of team deals so I can put it into a
table and graph the results. Here are the parameters:

Team A consists of the following members: John, Joe, Bob
Team B consists of the following members: Shannon, Susan, Sally

A B
1 John Susan
2 Joe Sally
3 Bob John
4 Susan Shannon
5 Sally Bob
6 Sally Shannon

I need a formula that will count Team A Deals (row 3) and Team B Deals (row
4/6) with the following results.

Team A Deals = 1
Team B Deals = 2

Help!!! Thanks in advance... you guys are the BEST!

Counting Team Deals
 

Try this:

With
A1:B6 containing your posted list

And
D1:D4 containing Team1 members (D1: Team1)
E1:E4 containing Team2 members (E1: Team2)

Then
G1: Team1 Deals
H1:
=SUMPRODUCT(ISNUMBER(MATCH(A1:A10,D2:D4,0))*ISNUMB ER(MATCH(B1:B10,D2:D4,0)))

G2: Team2 Deals
H2:
=SUMPRODUCT(ISNUMBER(MATCH(A1:A10,E2:E4,0))*ISNUMB ER(MATCH(B1:B10,E2:E4,0)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Shirley" wrote:

Is there a formula to highlight and/or count the number of team deals.
Ideally, I'd like to count the number of team deals so I can put it into a
table and graph the results. Here are the parameters:

Team A consists of the following members: John, Joe, Bob
Team B consists of the following members: Shannon, Susan, Sally

A B
1 John Susan
2 Joe Sally
3 Bob John
4 Susan Shannon
5 Sally Bob
6 Sally Shannon

I need a formula that will count Team A Deals (row 3) and Team B Deals (row
4/6) with the following results.

Team A Deals = 1
Team B Deals = 2

Help!!! Thanks in advance... you guys are the BEST!