Countif with offset

[Gerry_F]

I have a column with team names and game results in cells offset 2 down always in this order. I am trying to count the times a team is played and capture the total results using offset in a countif but without success.
If there is a working formula, I'll also add another criteria offset down 1 cell for game type
Any help would be appreciated as this is for my sons hockey team

Formulas in D3 is =COUNTIFS($A:$A,$C3,$A:$A,OFFSET($C3,2,0)=D$2)
Also tried an Index in J3 (=COUNTIFS(INDEX($A$7:$A$336,MATCH($H3,$A$7:$A$336,0)+2),J$2))

 

[Cubist]

Try:

Book1
	A	B	C	D	E
1			Exhibition
2				win	loss
3			team1	1	1
4			team2	0	1
5			team3	0	0
6
7	10/22/24
8	team1
9	exhibition
10	win
11
12	10/25/24
13	team2
14	exhibition
15	loss
16
17	10/28/24
18	team1
19	exhibition
20	loss
21
Sheet1

Cell Formulas
Range	Formula
D3:E5	D3	=COUNTIFS($A$8:$A$21,$C3,$A$9:$A$22,$C$1,$A$10:$A$23,D$2)

Side note: I'd recommend restructuring your data like this.

Book1
	A	B	C	D
23	Date	Team	Season	Outcome
24	10/22/24	team1	exhibition	win
25	10/25/24	team2	exhibition	loss
26	10/28/24	team1	exhibition	loss
Sheet1

Cell Formulas
Range	Formula
A24:D26	A24	=WRAPROWS(TOCOL(A7:A20,1),4)
Dynamic array formulas.

 

[kvsrinivasamurthy]

For 365 version. Slightly lengthy but spill over formula.
In D3

=LET(a,C3:C5,b,D2:F2,c,A8:A18,d,A10:A20,MAKEARRAY(ROWS(a),COLUMNS(b),LAMBDA(ro,cl,SUMPRODUCT((c=INDEX(a,ro))*(d=INDEX(b,cl))))))

 

[Cubist]

You can spill with COUNTIFS.

Book1
	A	B	C	D	E
1			Exhibition
2				win	loss
3			team1	1	1
4			team2	0	1
5			team3	0	0
6
7	10/22/24
8	team1
9	exhibition
10	win
11
12	10/25/24
13	team2
14	exhibition
15	loss
16
17	10/28/24
18	team1
19	exhibition
20	loss
Sheet1

Cell Formulas
Range	Formula
D3:E5	D3	=COUNTIFS(A8:A21,C$3:C$5,A9:A22,C1,A10:A23,D2:E2)
Dynamic array formulas.

 

[Gerry_F]

For 365 version. Slightly lengthy but spill over formula.
In D3

=LET(a,C3:C5,b,D2:F2,c,A8:A18,d,A10:A20,MAKEARRAY(ROWS(a),COLUMNS(b),LAMBDA(ro,cl,SUMPRODUCT((c=INDEX(a,ro))*(d=INDEX(b,cl))))))

View attachment 120346

Works but doesn't take Exhibition vs Regular Season to account

 

[Gerry_F]

Try:

Book1
	A	B	C	D	E
1			Exhibition
2				win	loss
3			team1	1	1
4			team2	0	1
5			team3	0	0
6
7	10/22/24
8	team1
9	exhibition
10	win
11
12	10/25/24
13	team2
14	exhibition
15	loss
16
17	10/28/24
18	team1
19	exhibition
20	loss
21
Sheet1

Cell Formulas
Range	Formula
D3:E5	D3	=COUNTIFS($A$8:$A$21,$C3,$A$9:$A$22,$C$1,$A$10:$A$23,D$2)

Side note: I'd recommend restructuring your data like this.

Book1
	A	B	C	D
23	Date	Team	Season	Outcome
24	10/22/24	team1	exhibition	win
25	10/25/24	team2	exhibition	loss
26	10/28/24	team1	exhibition	loss
Sheet1

Cell Formulas
Range	Formula
A24:D26	A24	=WRAPROWS(TOCOL(A7:A20,1),4)
Dynamic array formulas.

There's a reason behind it's laid out as I did - players per game are listed in the space along with goalies. But I may need to lay it out slightly different. Thanks

 

[kvsrinivasamurthy]

For exibition In D3

=LET(a,C3:C5,b,D2:F2,c,A8:A18,d,A10:A20,e,A9:A19,MAKEARRAY(ROWS(a),COLUMNS(b),LAMBDA(ro,cl,SUMPRODUCT((c=INDEX(a,ro))*(d=INDEX(b,cl))*(e="exhibition")))))

For Regular Season in I3

=LET(a,H3:H5,b,I2:K2,c,A8:A18,d,A10:A20,e,A9:A19,MAKEARRAY(ROWS(a),COLUMNS(b),LAMBDA(ro,cl,SUMPRODUCT((c=INDEX(a,ro))*(d=INDEX(b,cl))*(e="Regular Season")))))

Change the ranges suitably.