Countifs? Count events in a range if something doesn't happen in another range

[AYouQueTai]

Hello,

In hockey, teams usually play 5-6 consecutive games at home and then 5-6 games on the road.
I am trying to accomplish two things,
1 - Count the number of consecutive games the visiting team will play as visitors before returning home.
2 - Figure out which game, in the range of consecutive games, they are currently playing. IE Game 2 of 6

So using Nashville as an example
Cell F2 - Formula 1- count the number of consecutive games Nashville (Column H) plays as Visitor before returning home. In this case they are listed in H2 and H22 then at home at K45. The moment they play a game at home (Column K) the visitor count ends at 2.
Formula 2- Identify which game in the road trip they are currently on. IE. F2 would be 1 of 2. F22 would be 2 of 2.

I've tried coming up with a solution using countifs and Sumproduct but can't figure it out.
Any suggestions?

NHL 2023-2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	GameID	Status	Day	Date	Time (AST)	VGC	VDBG	Visitor	HGC	HDBG	Home	Arena	EV
2	020001	Completed	Tuesday	Oct-10	18:30		50	Nashville		50	Tampa Bay
3	020002	Completed	Tuesday	Oct-10	21:00		50	Chicago		50	Pittsburgh
4	020003	Completed	Tuesday	Oct-10	23:45		50	Seattle		50	Vegas
5	020004	Completed	Wednesday	Oct-11	20:00		50	Ottawa		50	Carolina
6	020005	Completed	Wednesday	Oct-11	20:00		50	Montreal		50	Toronto
7	020006	Completed	Wednesday	Oct-11	20:30		0	Chicago		50	Boston
8	020007	Completed	Wednesday	Oct-11	23:00		50	Winnipeg		50	Calgary
9	020008	Completed	Wednesday	Oct-11	23:15		50	Colorado		50	Los Angeles
10	020009	Completed	Wednesday	Oct-11	23:15		50	Edmonton		50	Vancouver
11	020010	Completed	Thursday	Oct-12	20:00		50	NY Rangers		50	Buffalo
12	020011	Completed	Thursday	Oct-12	20:00		50	Philadelphia		50	Columbus
13	020012	Completed	Thursday	Oct-12	20:30		50	Detroit		50	New Jersey
14	020013	Completed	Thursday	Oct-12	21:00		50	St-Louis		50	Dallas
15	020014	Completed	Thursday	Oct-12	21:00		50	Florida		50	Minnesota
16	020015	Completed	Thursday	Oct-12	21:00		1	Seattle		1	Nashville
17	020016	Completed	Thursday	Oct-12	23:30		1	Vegas		50	San Jose
18	020017	Completed	Friday	Oct-13	20:00		50	Arizona		0	New Jersey
19	020018	Completed	Friday	Oct-13	20:30		2	Pittsburgh		50	Washington
20	020019	Completed	Saturday	Oct-14	14:15		1	Philadelphia		2	Ottawa
21	020020	Completed	Saturday	Oct-14	17:15		1	Florida		2	Winnipeg
22	020021	Completed	Saturday	Oct-14	20:00		1	Nashville		2	Boston
23	020022	Completed	Saturday	Oct-14	20:00		1	NY Rangers		1	Columbus
24	020023	Completed	Saturday	Oct-14	20:00		3	Tampa Bay		1	Detroit
25	020024	Completed	Saturday	Oct-14	20:00		2	Chicago		2	Montreal
26	020025	Completed	Saturday	Oct-14	20:00		2	Calgary		0	Pittsburgh
27	020026	Completed	Saturday	Oct-14	20:00		1	Minnesota		2	Toronto
28	020027	Completed	Saturday	Oct-14	20:30		1	Buffalo		50	NY Islanders
29	020028	Completed	Saturday	Oct-14	21:00		1	Seattle		1	St-Louis
30	020029	Completed	Saturday	Oct-14	23:00		2	Vancouver		2	Edmonton
31	020030	Completed	Saturday	Oct-14	23:00		2	Colorado		1	San Jose
32	020031	Completed	Saturday	Oct-14	23:00		50	Anaheim		1	Vegas
33	020032	Completed	Saturday	Oct-14	23:30		2	Carolina		2	Los Angeles
34	020033	Completed	Sunday	Oct-15	20:00		0	Tampa Bay		0	Ottawa
35	020034	Completed	Sunday	Oct-15	21:30		0	Carolina		0	Anaheim
36	020035	Completed	Monday	Oct-16	20:00		1	Detroit		1	Columbus
37	020036	Completed	Monday	Oct-16	20:00		1	Florida		2	New Jersey
38	020037	Completed	Monday	Oct-16	20:00		2	Arizona		1	NY Rangers
39	020038	Completed	Monday	Oct-16	20:00		1	Chicago		1	Toronto
40	020039	Completed	Monday	Oct-16	20:00		1	Calgary		2	Washington
41	020042	Completed	Tuesday	Oct-17	19:00		2	Vancouver		2	Philadelphia
42	020041	Completed	Tuesday	Oct-17	20:00		2	Minnesota		2	Montreal
43	020040	Completed	Tuesday	Oct-17	20:30		1	Tampa Bay		2	Buffalo
44	020043	Completed	Tuesday	Oct-17	20:30		0	Arizona		2	NY Islanders
45	020044	Completed	Tuesday	Oct-17	21:00		2	Edmonton		2	Nashville
46	020045	Completed	Tuesday	Oct-17	21:00		2	Los Angeles		2	Winnipeg
47	020046	Completed	Tuesday	Oct-17	23:00		2	Colorado		2	Seattle
48	020047	Completed	Tuesday	Oct-17	23:30		1	Carolina		2	San Jose
49	020048	Completed	Tuesday	Oct-17	23:30		4	Dallas		2	Vegas
50	020049	Completed	Wednesday	Oct-18	20:00		1	Washington		2	Ottawa
51	020050	Completed	Wednesday	Oct-18	20:30		3	Pittsburgh		1	Detroit
Schedule

Cell Formulas
Range	Formula
C2:C51	C2	=TEXT([@Date],"dddd")
G2:G51	G2	=IFERROR(DAYS([@Date]-1,IFERROR(@INDEX([Date],AGGREGATE(14,6,(ROW([Visitor])-ROW(Schedule!$E$2)+1)/([Date]<$D2)/(($H2=[Visitor])+($H2=[Home])),ROWS($G2:$G2))),"")),50)
J2:J51	J2	=IFERROR(DAYS([@Date]-1,IFERROR(@INDEX([Date],AGGREGATE(14,6,(ROW([Visitor])-ROW(Schedule!$E$2)+1)/([Date]<$D2)/(($K2=[Visitor])+($K2=[Home])),ROWS($J2:$J2))),"")),50)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:M884	Expression	=$M2=""	text	NO
M2:M884	Expression	=$M2="Y"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2:B51	List	=INDIRECT("StatusDrop[Status]")
K2:K51	List	=INDIRECT("Teams[Teams]")
H2:H51	List	=INDIRECT("Teams[Teams]")

Thanks,

 

[jasonb75]

Try this in F2 and see if it gives the correct results for part 1 of your question. It gives a different result for your example but I note that you appear to have overlooked Nashville's home game in K16.

=INDEX(FREQUENCY(IF([Visitor]=[@Visitor],ROW([Visitor])),IF(COUNTIF([Home],[@Visitor]),IF([Home]=[@Visitor],ROW([Visitor])),MAX(ROW([Home])))),COUNTIF([[#Headers],[Home]]:[@Home],[@Visitor])+1)

 

[AYouQueTai]

You're right I did overlook K16, sorry about that. But thank you very much it works great for that part of the problem