Counting streak in a table column

[SWHarmon79]

I want to count the current streak of wins or losses. I have a workbook with two worksheets. One worksheet is a large dataset of games and I want to count the current streak based on a couple of criteria. Below are screenshots of part of both sheets. As an example from the screenshot below CRD = 1 ATL = 2 and if there were a losing streak it would show a negative number.

I want to count the current streak based on the team in column H and the week in A2. I can count all wins or losses in the column using =COUNTA(Data[Win])-MATCH(1,INDEX(1/(Data[Win]=1),0)) But I want to only count the current streak for the selected team and week. I have tried all sorts of combinations but I can't seem to get anything to work.

 

[Eric W]

I assume that you want the streak starting at the given week in A2, and go backwards. Try:

Book1
	A	B	C	D	E	F	G	H	I
1		Week	Team		Win		Week	Team	Streak
2		1	CRD		0		13	ATL	2
3		2	CRD		0			CRD	1
4		3	CRD		1
5		4	CRD		0
6		5	CRD		0
7		6	CRD		0
8		7	CRD		0
9		8	CRD		0
10		9	CRD		0
11		10	CRD		1
12		11	CRD		0
13		12	CRD		0
14		13	CRD		1
15		15	CRD		0
16		16	CRD		0
17		17	CRD		0
18		18	CRD		0
19		1	ATL		1
20		2	ATL		1
21		3	ATL		0
22		4	ATL		0
23		5	ATL		1
24		6	ATL		0
25		7	ATL		1
26		8	ATL		0
27		9	ATL		0
28		10	ATL		0
29		12	ATL		1
30		13	ATL		1
31		14	ATL		0
32		15	ATL		0
33		16	ATL		0
34		17	ATL		0
35
Sheet3

Cell Formulas
Range	Formula
I2:I3	I2	=LET(t,FILTER($E$2:$E$100,($B$2:$B$100<=$G$2)*($C$2:$C$100=H2)),r,ROWS(t),wl,INDEX(t,r),lw,1-wl,x,IFERROR(XMATCH(lw,t,0,-1),0),IF(wl,r-x,x-r))

Adjust the ranges to your sheets.

 

[SWHarmon79]

I assume that you want the streak starting at the given week in A2, and go backwards. Try:

Book1
	A	B	C	D	E	F	G	H	I
1		Week	Team		Win		Week	Team	Streak
2		1	CRD		0		13	ATL	2
3		2	CRD		0			CRD	1
4		3	CRD		1
5		4	CRD		0
6		5	CRD		0
7		6	CRD		0
8		7	CRD		0
9		8	CRD		0
10		9	CRD		0
11		10	CRD		1
12		11	CRD		0
13		12	CRD		0
14		13	CRD		1
15		15	CRD		0
16		16	CRD		0
17		17	CRD		0
18		18	CRD		0
19		1	ATL		1
20		2	ATL		1
21		3	ATL		0
22		4	ATL		0
23		5	ATL		1
24		6	ATL		0
25		7	ATL		1
26		8	ATL		0
27		9	ATL		0
28		10	ATL		0
29		12	ATL		1
30		13	ATL		1
31		14	ATL		0
32		15	ATL		0
33		16	ATL		0
34		17	ATL		0
35
Sheet3

Cell Formulas
Range	Formula
I2:I3	I2	=LET(t,FILTER($E$2:$E$100,($B$2:$B$100<=$G$2)*($C$2:$C$100=H2)),r,ROWS(t),wl,INDEX(t,r),lw,1-wl,x,IFERROR(XMATCH(lw,t,0,-1),0),IF(wl,r-x,x-r))

Adjust the ranges to your sheets.

Thank you that worked great. Are there any resources that you can recommend that will help me understand that formula?

 

[Eric W]

Well, you can always Google the individual functions within it, and the Evaluate Formula tool on the Formulas tab is helpful too, which lets you go through each step in the formula. But I'll give you a high level overview of it.

The LET function allows you to create intermediate results that you can use multiple times in the formula. LET(a, A1+5, . . . for example assigns the value of A1+5 to the variable a, and you can use a later on. So:

LET(t,FILTER($E$2:$E$100,($B$2:$B$100<=$G$2)*($C$2:$C$100=H2)), . . .

assigns the value of the FILTER function to t. The FILTER function filters the E column, only keeping the rows that are less than or equal to the week you want (G2), and have the team you want (H2). So this leaves you the win/loss record of the team in question up to the current week.

The r,ROWS(t), section then tells you how many rows there are in the table we just extracted.

The wl,INDEX(t,r), section gives you the value (1 or 0) of the last entry in the table, so the type of game (a win or a loss) that the streak is.

The lw,1-wl, part gives you the opposite of the streak. We'll use this to search for the end of the streak.

The x,IFERROR(XMATCH(lw,t,0,-1),0), section looks for the end of the streak. The lw is the opposite which we're looking for, the t is the table we extracted, the 0 means we want an exact match, and the -1 means we're searching from the bottom going backwards. The IFERROR part comes into play if XMATCH can't find a match. This means that the streak started with the first game of the table. The 0 value will make the last step work out in that case.

And finally, the ,IF(wl,r-x,x-r)) part calculates the length of the streak. The last row r, minus the row where the streak ends x, gives you the streak length. If the current streak type is losses (wl = 0), then it calculates x-r instead, which gives you the same length, but the sign is negative.

Hope this helps!