Calculate a winning streak per person

[Shales]

Hi, I am building a basic data table for some stats on my 5-aside football group for a bit of fun. I am wanting to calculate a current Win Streak per player so I can share the stats with my group before a match.

This is how my basic data recording looks;

On another tab, I am summarising the stats per Player;

I am looking for a formula that will calculate a current Win Streak by counting the Consecutive Ws from the first sheet of results. I cannot seem to work it out per player.

Any help would be fantastic.

Thank you

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

current Win Streak by counting the Consecutive Ws

Starting from the latest date and counting backwards?

 

[Shales]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Starting from the latest date and counting backwards?

Hi Peter. Yes, starting from the most recent date and working backwards. Thanks.

I will update my Acc details.

 

[Peter_SSs]

Thanks for updating your details.

Yes, starting from the most recent date and working backwards.

Is this the sort of thing you mean then?

22 09 20.xlsm
	A	B	C	D	E	F
1	Date	Players	W/L		Player	Win Streak
2	9/09/2022	Biggsy	L		Biggsy	0
3	9/09/2022	Gav	L		Gav	1
4	9/09/2022	Karl	L		Karl	0
5	9/09/2022	Tommy	L		Tommy	0
6	9/09/2022	Powns	L		Powns	1
7	9/09/2022	Martin	W		Martin	2
8	9/09/2022	Doug	W		Doug	0
9	9/09/2022	Hatty	W		Hatty	1
10	9/09/2022	Jase	W		Jase	1
11	9/09/2022	Dave	W		Dave	2
12	16/09/2022	Doug	L		Darren	0
13	16/09/2022	Darren	L		Bissy	0
14	16/09/2022	Bissy	L		Guy	1
15	16/09/2022	Biggsy	L
16	16/09/2022	Tommy	L
17	16/09/2022	Gav	W
18	16/09/2022	Guy	W
19	16/09/2022	Martin	W
20	16/09/2022	Powns	W
21	16/09/2022	Dave	W
Win Streak

Cell Formulas
Range	Formula
E2:E14	E2	=UNIQUE(B2:B21)
F2:F21	F2	=IF(E2="","",LET(f,FILTER(C2:C21,B2:B21=E2),IFNA(ROWS(f)-XMATCH("L",f,0,-1),ROWS(f))))
Dynamic array formulas.

 

[Gloffo]

There's a neat solution from Peter_SSs above. I have an older version, så can't use XMATCH. This was my suggestion.

If you sort your data with the most recent game on top, you can then match what row you'll find the most recent loss, using this formula.

{=IFERROR(MATCH("L",IF('Games played'!$B$2:$B$40=A2,'Games played'!$C$2:$C$40),0),999)}

I have named the sheet with the results "Games played" and that it should be 39 rows. Just add more if needed. Also, if there is no loss at all, the result is 999, i.e. far down the sheet. Notice that it is a array formula. (ctrl+enter)

Now you know how many rows down you have you first loss. Then you only need to count the number of each name from the top, to the row above this row. I used INDIRECT to make this formula.

{=COUNTIF(INDIRECT("'Games played'!$B$1:$B$"&IFERROR(MATCH("L",IF('Games played'!$B$2:$B$40=A1,'Games played'!$C$2:$C$40),0),999)-1),A1)}

 

[Shales]

@Peter_SSs Amazing! Yes that is what I am after. I took the formula and placed it another tab in my Doc and changed the cell refs etc. It works perfectly. Thank you very much!!

@Gloffo Thank you for your suggestions. I will explore this just to enhance my understandings.

Thank you both : )

 

[Peter_SSs]

@Peter_SSs Amazing! Yes that is what I am after. I took the formula and placed it another tab in my Doc and changed the cell refs etc. It works perfectly. Thank you very much!!

You are very welcome. Thanks for the follow-up.

BTW, I suggest that you investigate XL2BB for providing sample data in the future to make it easier for helpers by not having to manually type out sample data to test with.

@Gloffo Thank you for your suggestions. I will explore this just to enhance my understandings.

If you needed to do this without XMATCH, you could also do it like this and avoid both the sorting and the volatile function INDIRECT, like this. (Still need to adapt to different sheets but you are on top of that by the sound of it. )

22 09 20.xlsm
	A	B	C	D	E	F
1	Date	Players	W/L		Player	Win Streak
2	9/09/2022	Biggsy	L		Biggsy	0
3	9/09/2022	Gav	L		Gav	1
4	9/09/2022	Karl	L		Karl	0
5	9/09/2022	Tommy	L		Tommy	0
6	9/09/2022	Powns	L		Powns	1
7	9/09/2022	Martin	W		Martin	2
8	9/09/2022	Doug	W		Doug	0
9	9/09/2022	Hatty	W		Hatty	1
10	9/09/2022	Jase	W		Jase	1
11	9/09/2022	Dave	W		Dave	2
12	16/09/2022	Doug	L		Darren	0
13	16/09/2022	Darren	L		Bissy	0
14	16/09/2022	Bissy	L		Guy	1
15	16/09/2022	Biggsy	L
16	16/09/2022	Tommy	L
17	16/09/2022	Gav	W
18	16/09/2022	Guy	W
19	16/09/2022	Martin	W
20	16/09/2022	Powns	W
21	16/09/2022	Dave	W
Win Streak (2)

Cell Formulas
Range	Formula
F2:F14	F2	=SUMPRODUCT(--(C$2:C$21="W"),--(B$2:B$21=E2),--(ROW(C$2:C$21)>IFERROR(AGGREGATE(14,6,ROW(C$2:C$21)/((B$2:B$21=E2)*(C$2:C$21="L")),1),0)))