A count consecutive formula in Excel

[theYaniac]

Is there a way to count consecutive cells in excel? I have a sheet with a column "X" for wins and losses. I would like to count the current consecutive win or loss streak. If the column's most recent values are 3 consecutive rows with "W" as a value should be 3. If the next row in X has an L the cell counting wins would be zero and the cell counting the losses would be 1.

 

[JGordon11]

Book2
	A	B	C	D	E
1	W/L
2	W		W	3
3	W		L	0
4	W
5	L
6	L
7	W
8	W
9	W
10
Sheet4

Cell Formulas
Range	Formula
D2	D2	=LEN(TEXTAFTER(TEXTJOIN("",1,Table3[W/L]),"L",-1,1))
D3	D3	=LEN(TEXTAFTER(TEXTJOIN("",1,Table3[W/L]),"W",-1,1))

 

[theYaniac]

Thank you

 

[Fluff]

A slight tweak to JGordon11's formula in-case there are only wins or losses, but not both

=LEN(TEXTAFTER(CONCAT(A2:A20),"L",-1,1,1))