Consecutive wins in a row in certain conditions

[clairecatx]

Hello. I'm trying to work out how to count max consecutive wins in a row under a range of conditions. For example, what is the max consecutive wins in a row in period 1 (Year 2020 to 2021) vs period 2 (Year 2022 to 2023)?

I'm using M365 and am aware of the =max(scan(...)) function, but this doesn't filter out wins that don't meet the condition specified. Tried using =maxif(max(scan(...)).criteria range 1, criteria) but no luck.

Does anyone have any advice on how to approach this?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E
1
2		1	W	1	4
3		1	W	2	3
4		1	W
5		1	W
6		1	L
7		1	L
8		1	L
9		1	L
10		1	W
11		1	W
12		2	L
13		2	L
14		2	L
15		2	L
16		2	W
17		2	W
18		2	L
19		2	W
20		2	W
21		2	W
Data

Cell Formulas
Range	Formula
E2:E3	E2	=MAX(FREQUENCY(IF(($B$2:$B$21=D2)*($C$2:$C$21="W"),ROW($C$2:$C$21)),IF(($B$2:$B$21=D2)*($C$2:$C$21<>"W"),ROW($C$2:$C$21))))

 

[felixstraube]

Another option:

Book12.xlsx
	A	B	C	D	E
1	Year	Period	W/L
2	2020	1	W	1	4
3	2020	1	W	2	3
4	2020	1	W
5	2020	1	W
6	2020	1	L
7	2021	1	L
8	2021	1	L
9	2021	1	L
10	2021	1	W
11	2021	1	W
12	2022	2	L
13	2022	2	L
14	2022	2	L
15	2022	2	L
16	2022	2	W
17	2023	2	W
18	2023	2	L
19	2023	2	W
20	2023	2	W
21	2023	2	W
Sheet5

Cell Formulas
Range	Formula
E2:E3	E2	=MAX(DROP(REDUCE(0, ($C$2:$C$21="W")*($B$2:$B$21=D2), LAMBDA(arr,new, VSTACK(arr, IF(new, TAKE(arr, -1) + 1, 0)))), 1))

 

[Fluff]

Another option with scan

=MAX(SCAN(0,FILTER(C2:C21,B2:B21=D2),LAMBDA(a,b,IF(b="W",a+1,0))))

 

[felixstraube]

Nice one.
I think the ranges should be fixed ($C$2:$C$21, $B$2:$B$21)

 

[clairecatx]

Thank you @Fluff and @felixstraube - all suggestions worked.

 

[Fluff]

Glad we could help & thanks for the feedback.