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.

 

[KRice]

See below for an alternative.

 

[KRice]

That's more involved because the current approach involves a sequential count moving down the column...working on an alternative. Here is one approach that should work. Enter the League Home Win Streak threshold in D2 and E2 returns the number of streaks >= that value. I left your helper column in this example for convenient reference, but it is not needed. I also split up the win streak by entering an "L" to test the formula.

Book1
	A	B	C	D	E	F	G
1				League Home Win Streak >=	Number		BB
2	League	A	W	4	2		0
3	League	H	W				1
4	League	H	L				0
5	League	H	W				1
6	League	A	L				1
7	League	H	W				2
8	League	A	L				2
9	League	H	W				3
10	League	H	W				4
11	FA Cup	A	W				4
12	FA Cup	A	L				4
13	League	A	W				4
14	League	H	D				0
15	League	A	D				0
16	League	H	W				1
17	League	H	L				0
18	League	A	W				0
19	League	A	L				0
20	League	H	W				1
21	League	H	W				2
22	League	A	D				2
23	League	H	W				3
24	League	A	W				3
25	League	H	W				4
26	League	A	W				4
27	League	A	W				4
28	League	A	D				4
29	League	A	L				4
30	League	H	W				5
31	League	A	W				5
32	FA Cup	H	L				5
33	League	A	L				5
34	League	H	L				0
Sheet1

Cell Formulas
Range	Formula
E2	E2	=LET(str,CONCAT(FILTER($C$2:$C$34,($A$2:$A$34="League")*($B$2:$B$34="H"))),Wstr,FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(str,"L","</y><y>"),"D","</y><y>")&"</y></x>","//y"),NconW,IFERROR(LEN(Wstr),0),SUM(--(NconW>=D2)))
G2:G34	G2	=IF(($A2="League")*($B2="H"),IF($C2="W",SUM(1,G1),0),N(G1))

 

[KRice]

Ignore this post...in the middle of editing

 

[KRice]

The above uses FILTER to extract the League Home games, then constructs a text string, which might consist of W, L, and D...let me know if anything else might appear there. The text string is then split using L and D as delimiters, leaving only win streaks...text strings consisting of just W's. The length of these strings is counted, and used for the final sum to determine how many exceed your inputted win streak threshold.

 

[Marvo]

Thank you Kirk, I'll get on to it and report back.

 

[Cubist]

Does this work? Change the "12" to your cell reference.

=LET(f,FILTER(C2:C34,(A2:A34="League")*(B2:B34="H")),SUM(--(SCAN(0,f,LAMBDA(a,b,IF(b="W",a+1,a)))=12)))

 

[KRice]

Just to confirm...your profile shows that you're using Excel 2021. Is that current? I ask because the text split operation in my formula uses an older method with the FILTERXML function. In Excel 365 this would be done more easily with TEXTSPLIT, but I don't believe that function is available in your version. Alternatively, in Excel 365, you could perform the sequential row-by-row tally using a LAMBDA function, as @Cubist has just suggested, but that may not be an option for you.

 

[Marvo]

No, no LAMBDA. I have office 2021

 

[Cubist]

My mistake. I thought I saw 365. Carry on.

EDIT: XL2021 doesn't have the array functions like LET and FILTER either. I guess this is why I assumed you have D365 from Kirk's solution.