Record versus Group

[bet1734]

Hi everybody

I am trying to figure out a method for showing the record a team has against a certain group of teams.

In the below, I have Teams A-D with their corresponding group in a table, West (Teams A & B) or East (Teams C & D). I also have 12 mock games listed between these teams, Home Team in column A, Away Team in B, Home Score in C, and Away Score in D.

I want to show all the times team A won against the East (teams C & D). I broke it down in columns F-H where I manually examinded the results of the game and entered a yes if the instance was true. Column F is for all the games a team from the East played (8 games). G is when team A played the East teams (4 games). H is when A beat the Eastern teams (3 games). Therefore, I would like to report that total value "3" in the corresponding table.

I know there's got to be a way to automate this somehow. The larger sheet has over a thousand games so I would prefer not to have to manually count each time, especially since more data will be entered. I thought about using a sumproduct function to somehow lookup the 3 parts but that hasn't yielded any positive results so far.

Appreciate any ideas you might have!

NHL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Home	Away	HS	AS		East Plays	A vs East	Win vs East		Team	Group
2	A	B	2	3		No	No	No		A	West
3	A	C	5	1		Yes	Yes	Yes		B	West
4	A	D	3	4		Yes	Yes	Yes		C	East
5	B	A	1	3		No	No	No		D	East
6	B	C	3	2		Yes	No	No
7	B	D	3	5		Yes	No	No		Team	East	West
8	C	A	4	1		Yes	Yes	No		A	3
9	C	B	3	2		Yes	No	No		B
10	C	D	1	0		Yes	No	No		C
11	D	A	4	5		Yes	Yes	Yes		D
12	D	B	1	4		Yes	No	No
13	D	C	2	1		Yes	No	No
14						8	4	3
15
16
Sheet1

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E				I	J	K	L
1	Home	Away	HS	AS						Team	Group
2	A	B	2	3						A	West
3	A	C	5	1						B	West
4	A	D	3	4						C	East
5	B	A	1	3						D	East
6	B	C	3	2
7	B	D	3	5						Team	East	West
8	C	A	4	1						A	2	1
9	C	B	3	2						B	2	1
10	C	D	1	0						C	1	2
11	D	A	4	5						D	1	2
12	D	B	1	4
13	D	C	2	1
14
Data

Cell Formulas
Range	Formula
K8:L11	K8	=SUMPRODUCT(($A$2:$A$13=$J8)*($C$2:$C$13>$D$2:$D$13)*(VLOOKUP($B$2:$B$13,$J$2:$K$5,2,0)=K$7))+SUMPRODUCT(($B$2:$B$13=$J8)*($D$2:$D$13>$C$2:$C$13)*(VLOOKUP($A$2:$A$13,$J$2:$K$5,2,0)=K$7))

PS H4 should be No not Yes

 

[bet1734]

Curse you manual calculation!! haha thanks.

For some reason I can get the formula to work on the small example but it doesn't work when I put it on the larger sheet. I can keep messing around with it.

 

[Eric W]

Hi Fluff, bet1734,

I tried Fluff's formula on my Excel, and it did not work. A VLOOKUP inside of an array function has always been a no-no. Evidently with the new calculation engine it's OK. But for those of us stuck in the past, we can try this:

Book3
	A	B	C	D	E				I	J	K	L
1	Home	Away	HS	AS						Team	Group
2	A	B	2	3						A	West
3	A	C	5	1						B	West
4	A	D	3	4						C	East
5	B	A	1	3						D	East
6	B	C	3	2
7	B	D	3	5						Team	East	West
8	C	A	4	1						A	2	1
9	C	B	3	2						B	2	1
10	C	D	1	0						C	1	2
11	D	A	4	5						D	1	2
12	D	B	1	4
13	D	C	2	1
14										Team	East	West
15										A	2	1
16										B	2	1
17										C	1	2
18										D	1	2
Sheet3

Cell Formulas
Range	Formula
K8:L11	K8	=SUMPRODUCT(($A$2:$A$13=$J8)*($C$2:$C$13>$D$2:$D$13)*COUNTIFS($J$2:$J$5,$B$2:$B$13,$K$2:$K$5,K$7))+SUMPRODUCT(($B$2:$B$13=$J8)*($D$2:$D$13>$C$2:$C$13)*COUNTIFS($J$2:$J$5,$A$2:$A$13,$K$2:$K$5,K$7))
K15:L18	K15	=SUMPRODUCT(($A$2:$A$13=$J15)*($C$2:$C$13>$D$2:$D$13)*ISNUMBER(MATCH($B$2:$B$13&K$14,$J$2:$J$5&$K$2:$K$5,0)))+SUMPRODUCT(($B$2:$B$13=$J15)*($D$2:$D$13>$C$2:$C$13)*ISNUMBER(MATCH($A$2:$A$13&K$14,$J$2:$J$5&$K$2:$K$5,0)))

The K8 formula uses COUNTIFS instead of VLOOKUP. However, bet1734 says he has Excel 2016, which doesn't have COUNTIFS, so bet1734, try the K15 formula.

 

[bet1734]

YES! That worked perfectly. Thank you Eric W!

 

[Eric W]

Glad we could help!