How to count a losing streak followed by winning streak

[Trie1977]

Hi everyone,

I need help on this formula;

Ex. How many times it occurs that a losing streak of 3 is followed by a winning streak of 1

Thank you all so much!

Tony.

STREAK TO STREAK.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	How to count a losing streak followed by winning streak
2
3	Ex. How many times it occurs that a losing streak of 3 is followed by a winning streak of 1
4
5	W/L	STREAK			STREAKS	COUNTS		STREAKS FOLLOWED BY
6	W	1			-8	0		-1	1	2	HAPPEND 2 TIMES		<<<----- HOW TO CALCULATE THIS?
7	W	2			-7	0		1	-1	3	HAPPEND 3 TIMES
8	W	3			-6	0		1	-2	ETC
9	L	-1			-5	0		-1	2	ETC
10	L	-2			-4	0
11	L	-3			-3	2
12	W	1			-2	2
13	W	1			-1	5
14	W	1			0	0
15	L	-1			1	7
16	W	1			2	2
17	L	-1			3	1
18	W	1			4	0
19	W	2			5	0
20	L	-1			6	0
21	L	-2			7	0
22	L	-3			8	0
23	W	1			9	0
24	L	-1
25
Sheet1

Cell Formulas
Range	Formula
F6:F23	F6	=COUNTIF($B:$B,E6)

 

[awoohaw]

Try this, you will probably need to enter it with CSE (CTRL-SHFT-ENTER):

=SUM(--(B6:B24&B7:B25="-31"))

 

[Trie1977]

Ok, thank you.
I wonder how the formula looks like in another view:

STREAK TO STREAK.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3	W/L	STREAK			1	2	3	4	5	6	7	8	9	10	-1	-2	-3	-4	-5	-6	-7	-8
4	LOSS	-1		-8	1
5	LOSS	-2		-7
6	LOSS	-3		-6
7	LOSS	-4		-5
8	LOSS	-5		-4
9	LOSS	-6		-3
10	LOSS	-7		-2
11	LOSS	-8		-1
12	WON	1		1
13	WON	2		2
14	WON	4		3
15	LOSS	-1		4
16	WON	1		5
17	LOSS	-1		6
18	WON	1		7
19	LOSS	-1		8
20	WON	1		9
21	LOSS	-1		10
22	WON	1
23	WON	2
24	WON	3				STREAK OF -8 FOLLOWED BY A STREAK OF 1, ET CETERA
25	LOSS	-1
26	LOSS	-2
27	WON	1
28	WON	2
29	WON	3
30	WON	4
31
Sheet2

 

[awoohaw]

Not really sure what this gets you with the way you have the headers for row and column set up. But I guess it give you trends or something.
but here:

mr excel questions 34.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3	W/L	STREAK			1	2	3	4	5	6	7	8	9	10	-1	-2	-3	-4	-5	-6	-7	-8
4	LOSS	-1		-8	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	LOSS	-2		-7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
6	LOSS	-3		-6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
7	LOSS	-4		-5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
8	LOSS	-5		-4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
9	LOSS	-6		-3	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
10	LOSS	-7		-2	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	LOSS	-8		-1	4	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
12	WON	1		1	0	3	0	0	0	0	0	0	0	0	3	0	0	0	0	0	0	0
13	WON	2		2	0	0	2	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	WON	4		3	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
15	LOSS	-1		4	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
16	WON	1		5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	LOSS	-1		6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	WON	1		7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	LOSS	-1		8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	WON	1		9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	LOSS	-1		10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22	WON	1
23	WON	2
24	WON	3				STREAK OF -8 FOLLOWED BY A STREAK OF 1, ET CETERA
25	LOSS	-1
26	LOSS	-2
27	WON	1
28	WON	2
29	WON	3
30	WON	4
Trie1997_2

Cell Formulas
Range	Formula
E4:V21	E4	=SUM(--($B$6:$B$30&$B$7:$B$31=$D4&E$3))

 

[Trie1977]

Hi, yes you're right. Thank you for your efforts, really appreciated!

Enjoy your day, today we celebrate our freedom here in the Netherlands.

 

[Trie1977]

Sorry, but I like to know 1 more thing here.
So now it is looking to the next cell.
What if I want to know let's say, the max of the following winning streak before a new loss occurs?
ex. STREAK OF -8 FOLLOWED BY A STREAK OF 3, ET CETERA

STREAK TO STREAK.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3	W/L	STREAK			1	2	3	4	5	6	7	8	9	10	-1	-2	-3	-4	-5	-6	-7	-8
4	LOSS	-1		-8			1
5	LOSS	-2		-7
6	LOSS	-3		-6
7	LOSS	-4		-5
8	LOSS	-5		-4
9	LOSS	-6		-3
10	LOSS	-7		-2
11	LOSS	-8		-1
12	WON	1		1
13	WON	2		2
14	WON	3		3
15	LOSS	-1		4
16	WON	1		5
17	LOSS	-1		6
18	WON	1		7
19	LOSS	-1		8
20	WON	1		9
21	LOSS	-1		10
22	WON	1
23	WON	2
24	WON	3				ex. STREAK OF -8 FOLLOWED BY A STREAK OF 3, ET CETERA
25	LOSS	-1
26	LOSS	-2
27	WON	1
28	WON	2
29	WON	3
30	WON	4
31
Sheet3

 

[Trie1977]

Anybody please?