Counting consecutive occurrences

[bmv505]

I have a spreadsheet with American football plays (the actual play by play data). I want to count the consecutive number of complete passes one specific player has. My spreadsheet has a column for play type, pass result, and QB (see image).

So in the specific example in the image, Penix completed three consecutive passes. Is there a formula that finds that out for me without me having to go through all the data and counting the consecutive completed passes?

 

[JGordon11]

Book1
	A	B	C	D	E
1	Pass	Complete		Maximum consecutive
2	Rush			5
3	Pass	Complete
4	Rush
5	Rush
6	Pass	Complete
7	Pass	Complete
8	Pass	Incomplete
9	Pass	Complete
10	Rush
11	Pass	Complete
12	Rush
13	Pass	Complete
14	Pass	Complete
15	Pass	Complete
16	Pass	Incomplete
17	Rush
18	Rush
19	Pass	Complete
20	Rush
21	Pass	Complete
22
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MAX(SCAN(0,LET(a,B1:B21, FILTER(a,a<>"")),LAMBDA(s,c,IF(c="Complete",s+1,0))))

 

[bmv505]

=MAX(SCAN(0,LET(a,B1:B21, FILTER(a,a<>"")),LAMBDA(s,c,IF(c="Complete",s+1,0))))

Really appreciate you.

How would you do it if you want to do it by game? So QB completed 6 straight in game 1 and finished with a completion, but then completed 5 to start game 2. How can I calculate per game?

 

[JGordon11]

Book1
	A	B	C	D	E	F	G
1	Game				Game	Maximum consecutive
2	1	Pass	Complete		1	4
3	1	Rush			2	4
4	1	Pass	Complete		3	1
5	1	Rush
6	1	Rush
7	1	Pass	Complete
8	1	Pass	Complete
9	1	Pass	Incomplete
10	1	Pass	Complete
11	2	Rush
12	2	Pass	Complete
13	2	Rush
14	2	Pass	Complete
15	2	Pass	Complete
16	2	Pass	Complete
17	3	Pass	Incomplete
18	3	Rush
19	3	Rush
20	3	Pass	Complete
21	3	Rush	Incomplete
22	3	Pass	Complete
23
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(A2:A22)
F2:F4	F2	=MAX(SCAN(0,LET(a,FILTER($A$2:$C$22, $A$2:$A$22=E2), b, INDEX(a,,3), FILTER(b,b<>"")),LAMBDA(s,c,IF(c="Complete",s+1,0))))
Dynamic array formulas.

 

[Peter_SSs]

Could it be this that you are after?

24 01 01.xlsm
	A	B	C	D	E
1	Game	Pass Result		Game	Max
2	1	Incomplete		1	6
3	1	Complete		2	5
4	1	Complete		3	0
5	1	Complete
6	1	Complete
7	1	Complete
8	1	Complete
9	1	Incomplete
10	1	Complete
11	2	Complete
12	2	Complete
13	2	Complete
14	2	Complete
15	2	Complete
16	3	Incomplete
17	3	Incomplete
18	3	Incomplete
Consecutive

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A18)
E2:E4	E2	=BYROW(D2#,LAMBDA(r,IFERROR(LEN(MAX(TEXTSPLIT(TRIM(CONCAT(IF(A$2:A$18=r,IF(B$2:B$18="Complete",1," "),"")))," ")+0)),0)))
Dynamic array formulas.