Removing the need of a helper column

[Marvo]

Good morning, yesterday I posed a question regarding the attached image and Stephen Crump kindly gave me the perfect solution.

=COUNTIFS(A2:A34,"League",B2:B34,"H",D2:D34,E14)

What has got me thinking is the solution doesn't reference column C at all and as column D is a helper column that refers to Column C, is it possible to "cut out the middle man" completely, meaning I could dispense with the helper column? It would be a great boon if so as the several helper columns (60 in all) really slow the workbook down.

The formula in D3 is =IF(($A3="League")*($B3="H"),IF($C3="W",SUM(1,D2),0),N(D2))

Copied down

Many thanks for any help offered and if it can't be done, so be it. Just wondered.

 

[Marvo]

I do.

 

[Marvo]

Strange things going on. Tried the "DRAWS, wouldn't work with F2 but put a D in T3 and use that, it works! So strange.

 

[KRice]

Okay...I don't see any reason why using C2 in the formula wouldn't work. I almost always avoid merged cells because it leads to confusion and can cause problems in certain formulas. I typically enter the value in the first cell and then select multiple cells and format them to "Center across selection", which gives the same appearance, but the first cell is always the one that contains the value, and it's easier to inspect. Try unmerging one of those cell groups and investigate further.

 

[Marvo]

Yes, I suspected that, didn't make any difference. I'll keep experimenting.

 

[Marvo]

So
C2 doesn't work in C4 but T2 does
F2 doesn't work in F4 but T3 does
I2 WORKS!!!!!!
This might be a case for Scully and Mulder.

Cobblers.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ALL-TIME RECORDS
2	CONSECUTIVES		W			D			L			GF			GA					W
3			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE			D
4	ALL	ALL	11	1	28/12/1899	6	2	1983 & 2011	7	4	1972, 1985, 1988 & 1992	28	1	03/10/1908	29	1	28/12/1965			L
5	Matches	H	14	1	16/04/1927	5	1	21/03/1981	6	2	1985 & 2002	54	1	28/01/0928	16	1	13/10/1900			GF
6		A	6	2	2004 & 2015	4	2	1913 & 1994	16	1	27/10/1906	19	1	27/03/1959	40	1	01/09/1926			GA
7	lge	ALL	11	1	28/12/1899	6	2	1983 & 2011	8	1	26/10/1935	28	1	29/08/2015	27	1	28/09/1965			ALL
8	Matches	H	12	2	1899 & 2027	5	1	21/03/1981	5	5	1905, 1985, 1999, 2002 & 2016	75	1	28/12/1926	19	1	13/10/1900			LGE
9		A	8	1	21/11/2015	5	1	06/09/1994	15	1	27/10/1906	18	1	27/03/1959	42	1	20/02/1965			H
10	LONGEST RUNS		NO L			NO D			NO W			NO GA			NO GF					A
11			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
12	ALL	ALL	27	1	23/01/2016	29	1	05/02/1927	19	2	1969 & 1993	6	2	1910 & 1930	7	1	07/04/1939
13	Matches	H	33	1	22/02/1913	28	1	31/08/1957	11	1	17/01/1990	9	1	25/08/1923	6	1	14/12/2002
14		A	14	1	13/02/2016	21	1	12/04/1908	28	1	27/01/1906	6	1	12/04/1997	7	2	1938 & 1995
15	lge	ALL	31	1	28/12/2015	29	1	05/02/1927	18	2	1969 & 2011	6	2	1930 & 1975	7	1	07/04/1939
16	Matches	H	33	1	22/02/1913	26	2	1898 & 1984	11	1	17/01/1990	9	1	25/08/1923	5	1	14/12/2002
17		A	19	1	31/10/2015	23	1	30/04/1966	33	1	01/01/1921	6	1	12/04/1997	8	1	28/03/1967
Records

Cell Formulas
Range	Formula
C4	C4	=LET(t,ALL!$K:$K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=$T$2,s),IF(t<>$T$2,s))))
C5	C5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t=T2,SEQUENCE(ROWS(t))),IF(t<>"W",SEQUENCE(ROWS(t))))))
C6	C6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E="A"),MAX(FREQUENCY(IF(t="W",SEQUENCE(ROWS(t))),IF(t<>"W",SEQUENCE(ROWS(t))))))
C7	C7	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t="W",SEQUENCE(ROWS(t))),IF(t<>"W",SEQUENCE(ROWS(t))))))
C8	C8	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="W",s),IF(t<>"W",s))))
C9	C9	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="W",s),IF(t<>"W",s))))
F4	F4	=LET(t,ALL!$K:$K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=T3,s),IF(t<>T3,s))))
F5	F5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t=T3,SEQUENCE(ROWS(t))),IF(t<>T3,SEQUENCE(ROWS(t))))))
F6	F6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E="A"),MAX(FREQUENCY(IF(t="D",SEQUENCE(ROWS(t))),IF(t<>"D",SEQUENCE(ROWS(t))))))
F7	F7	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t="D",SEQUENCE(ROWS(t))),IF(t<>"D",SEQUENCE(ROWS(t))))))
F8	F8	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="D",s),IF(t<>"D",s))))
F9	F9	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="D",s),IF(t<>"D",s))))
I4	I4	=LET(t,ALL!K:K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=I2,s),IF(t<>I2,s))))
I5	I5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t=I2,SEQUENCE(ROWS(t))),IF(t<>I2,SEQUENCE(ROWS(t))))))
I6	I6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E="A"),MAX(FREQUENCY(IF(t="L",SEQUENCE(ROWS(t))),IF(t<>"L",SEQUENCE(ROWS(t))))))
I7	I7	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t="L",SEQUENCE(ROWS(t))),IF(t<>"L",SEQUENCE(ROWS(t))))))
I8	I8	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="L",s),IF(t<>"L",s))))
I9	I9	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="L",s),IF(t<>"L",s))))
L4	L4	=LET(t,ALL!L:L,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
L5	L5	=LET(t,FILTER(ALL!L:L,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L6	L6	=LET(t,FILTER(ALL!L:L,ALL!E:E="A"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L7	L7	=LET(t,FILTER(ALL!$L:$L,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L8	L8	=LET(t,FILTER(ALL!$L:$L,(ALL!$J:$J="League")*(ALL!$E:$E="H")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L9	L9	=LET(t,FILTER(ALL!$L:$L,(ALL!$J:$J="League")*(ALL!$E:$E="A")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O4	O4	=LET(t,ALL!$M:$M,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
O5	O5	=LET(t,FILTER(ALL!M:M,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O6	O6	=LET(t,FILTER(ALL!M:M,ALL!E:E="A"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O7	O7	=LET(t,FILTER(ALL!$M:$M,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O8	O8	=LET(t,FILTER(ALL!$M:$M,(ALL!$J:$J="League")*(ALL!$E:$E="H")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O9	O9	=LET(t,FILTER(ALL!$M:$M,(ALL!$J:$J="League")*(ALL!$E:$E="A")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
C12	C12	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"L",s),IF(t="L",s))))
C13	C13	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"L",s),IF(t="L",s))))
C14	C14	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"L",s),IF(t="L",s))))
C15	C15	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"L",s),IF(t="L",s))))
C16	C16	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"L",s),IF(t="L",s))))
C17	C17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"L",s),IF(t="L",s))))
F12	F12	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"D",s),IF(t="D",s))))
F13	F13	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"D",s),IF(t="D",s))))
F14	F14	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"D",s),IF(t="D",s))))
F15	F15	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"D",s),IF(t="D",s))))
F16	F16	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"D",s),IF(t="D",s))))
F17	F17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"D",s),IF(t="D",s))))
I12	I12	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"W",s),IF(t="W",s))))
I13	I13	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"W",s),IF(t="W",s))))
I14	I14	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"W",s),IF(t="W",s))))
I15	I15	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"W",s),IF(t="W",s))))
I16	I16	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"W",s),IF(t="W",s))))
I17	I17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"W",s),IF(t="W",s))))
L12	L12	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L13	L13	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L14	L14	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L15	L15	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L16	L16	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L17	L17	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O12	O12	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O13	O13	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O14	O14	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O15	O15	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O16	O16	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O17	O17	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))

 

[Marvo]

So listen, I cleared everything out of the cell in C2 with the eraser thing, then put a W back and it worked. Super. Did the same with F2, no joy. This is crazy.

 

[Marvo]

And now I've moved the "D" to G2 and it works. Mind-blowing. Anyway it means I do not need a helper column now in T.

 

[Marvo]

Okay, I've completed the box, the primaries are in PINK. I've realised you don't need ALL because if you don't have League in the formula it defaults to all.

Just to let you know, I completed the Draws using G2, then when I'd done, cut and pasted G2 into F2 and it worked. If you know why you're a better man than me.

Cobblers.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ALL-TIME RECORDS
2	CONSECUTIVES		W	Wins		D	Draws		L	Defeats		GF			GA
3			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
4	ALL	ALL	11	1	28/12/1899	6	2	1983 & 2011	7	4	1972, 1985, 1988 & 1992	28	1	03/10/1908	29	1	28/12/1965
5	Matches	H	14	1	16/04/1927	5	1	21/03/1981	6	2	1985 & 2002	54	1	28/01/0928	16	1	13/10/1900
6		A	6	2	2004 & 2015	4	2	1913 & 1994	16	1	27/10/1906	19	1	27/03/1959	40	1	01/09/1926
7	League	ALL	11	1	28/12/1899	6	2	1983 & 2011	8	1	26/10/1935	28	1	29/08/2015	27	1	28/09/1965
8	Matches	Home	12	2	1899 & 2027	5	1	21/03/1981	5	5	1905, 1985, 1999, 2002 & 2016	75	1	28/12/1926	19	1	13/10/1900
9		Away	8	1	21/11/2015	5	1	06/09/1994	15	1	27/10/1906	18	1	27/03/1959	42	1	20/02/1965
10	LONGEST RUNS		NO L			NO D			NO W			NO GA			NO GF
11			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
12	ALL	ALL	27	1	23/01/2016	29	1	05/02/1927	19	2	1969 & 1993	6	2	1910 & 1930	7	1	07/04/1939
13	Matches	H	33	1	22/02/1913	28	1	31/08/1957	11	1	17/01/1990	9	1	25/08/1923	6	1	14/12/2002
14		A	14	1	13/02/2016	21	1	12/04/1908	28	1	27/01/1906	6	1	12/04/1997	7	2	1938 & 1995
15	lge	ALL	31	1	28/12/2015	29	1	05/02/1927	18	2	1969 & 2011	6	2	1930 & 1975	7	1	07/04/1939
16	Matches	H	33	1	22/02/1913	26	2	1898 & 1984	11	1	17/01/1990	9	1	25/08/1923	5	1	14/12/2002
17		A	19	1	31/10/2015	23	1	30/04/1966	33	1	01/01/1921	6	1	12/04/1997	8	1	28/03/1967
Records

Cell Formulas
Range	Formula
C4	C4	=LET(t,ALL!$K:$K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=$C$2,s),IF(t<>$C$2,s))))
C5	C5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B5),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
C6	C6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B6),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
C7	C7	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
C8	C8	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A7)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=C2,s),IF(t<>"W",s))))
C9	C9	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A7)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=C2,s),IF(t<>C2,s))))
F4	F4	=LET(t,ALL!$K:$K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
F5	F5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B5),MAX(FREQUENCY(IF(t=F2,SEQUENCE(ROWS(t))),IF(t<>F2,SEQUENCE(ROWS(t))))))
F6	F6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B6),MAX(FREQUENCY(IF(t=F2,SEQUENCE(ROWS(t))),IF(t<>F2,SEQUENCE(ROWS(t))))))
F7	F7	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J=A7),MAX(FREQUENCY(IF(t=F2,SEQUENCE(ROWS(t))),IF(t<>F2,SEQUENCE(ROWS(t))))))
F8	F8	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A7)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
F9	F9	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="D",s),IF(t<>"D",s))))
I4	I4	=LET(t,ALL!K:K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=I2,s),IF(t<>I2,s))))
I5	I5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B5),MAX(FREQUENCY(IF(t=I2,SEQUENCE(ROWS(t))),IF(t<>I2,SEQUENCE(ROWS(t))))))
I6	I6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B6),MAX(FREQUENCY(IF(t=I2,SEQUENCE(ROWS(t))),IF(t<>I2,SEQUENCE(ROWS(t))))))
I7	I7	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J=A7),MAX(FREQUENCY(IF(t=I2,SEQUENCE(ROWS(t))),IF(t<>I2,SEQUENCE(ROWS(t))))))
I8	I8	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A7)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
I9	I9	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A7)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=I2,s),IF(t<>I2,s))))
L4	L4	=LET(t,ALL!L:L,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
L5	L5	=LET(t,FILTER(ALL!L:L,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L6	L6	=LET(t,FILTER(ALL!L:L,ALL!E:E="A"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L7	L7	=LET(t,FILTER(ALL!$L:$L,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L8	L8	=LET(t,FILTER(ALL!$L:$L,(ALL!$J:$J="League")*(ALL!$E:$E="H")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L9	L9	=LET(t,FILTER(ALL!$L:$L,(ALL!$J:$J="League")*(ALL!$E:$E="A")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O4	O4	=LET(t,ALL!$M:$M,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
O5	O5	=LET(t,FILTER(ALL!M:M,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O6	O6	=LET(t,FILTER(ALL!M:M,ALL!E:E="A"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O7	O7	=LET(t,FILTER(ALL!$M:$M,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O8	O8	=LET(t,FILTER(ALL!$M:$M,(ALL!$J:$J="League")*(ALL!$E:$E="H")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O9	O9	=LET(t,FILTER(ALL!$M:$M,(ALL!$J:$J="League")*(ALL!$E:$E="A")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
C12	C12	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C13	C13	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C14	C14	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C15	C15	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C16	C16	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C17	C17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
F12	F12	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F13	F13	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F14	F14	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F15	F15	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F16	F16	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F17	F17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
I12	I12	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I13	I13	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I14	I14	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I15	I15	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I16	I16	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I17	I17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A7)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
L12	L12	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L13	L13	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L14	L14	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L15	L15	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L16	L16	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L17	L17	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O12	O12	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O13	O13	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O14	O14	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O15	O15	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O16	O16	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O17	O17	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))

 

[KRice]

I haven't been able to reproduce the odd cell referencing behavior, but suspect it's due to merged cells. Although after unmerging them and confirming that the desired lookup value is in the correct cells, I don't understand why the formula would not work. At times, I've had formulas that seem to not compute correctly, and out of desperation have copied nearly the entire formula to the clipboard, deleted the cell contents...then repasted the bulk of the formula, and manually enter the few characters before/after to complete it...and discovered that it then works correctly. But I don't know why.

I still do not follow the column A idea. You have All, League, Matches, and lge in that column. Going row-by-row, how would you know which of those to apply to your match-type filter? You have how many different categories there?...I suppose the data table might show many: league matchups, and maybe tournaments with various names? But in the summary table, don't you have just two: either League or All?

 

[Marvo]

Yes, that's all it is. It's either all games or it's League games. Just two variables.
You wouldn't have consecutive Cup games, they are too far apart and the data would not make any sense anyway. Think of it like this. You've got a team in the bottom division going great guns, haven't lost for 30 games. They then get drawn away at Manchester City and unsurprisingly they lose. So their long unbeaten is over but not if you are counting just League games, then the run continues.

Column A can be safely ignored, the only date that matters within in it is A7 and I can move that if its a problem? The boxes could be set up differently and given a heading League or All.