Formula to create consecutive wins/losses

[James8761]

Hi All,

I'm working on trying to produce some stats for a football league I help run. As the results come in I just put a W or a L against the team name to show if they won or lost. The data is in columns F2:G1255. An example is show below.

I want to create a formula that shows the most amount of consecutive losses "L" a team has had. The answer I would want for Baystown in the example shown would be 4.

Then I need to create a formula for the most amount of consecutive Wins "W" a team has had. The answer I would want for Globe in the example shown would be 3.

Does anyone know if the above formulas are possible please?

Kind regards,
James

Baystown	L
Globe	W
North Shields	L
Eastbridge	W
Baystown	L
Baystown	L
North Shields	W
Eastbridge	W
Globe	W
Globe	W
North Shields	L
Baystown	L
Baystown	W
Globe	L
Globe	W
North Shields	W
Eastbridge	L
Eastbridge	W

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Try

	F​	G​	H​	I​	J​	K​
1​	Team​	Result​		Team​	L or W?​	Formula​
2​	Baystown​	L​		Baystown​	L​	4​
3​	Globe​	W​		Globe​	W​	3​
4​	North Shields​	L​
5​	Eastbridge​	W​
6​	Baystown​	L​
7​	Baystown​	L​
8​	North Shields​	W​
9​	Eastbridge​	W​
10​	Globe​	W​
11​	Globe​	W​
12​	North Shields​	L​
13​	Baystown​	L​
14​	Baystown​	W​
15​	Globe​	L​
16​	Globe​	W​
17​	North Shields​	W​
18​	Eastbridge​	L​
19​	Eastbridge​	W​
20​

Criteria in columns I:J

Array formula in K2 copied down
=MAX(FREQUENCY(IF(F$2:F$1255=I2,IF(G$2:G$1255=J2,ROW(G$2:G$1255)-ROW(G$2)+1)),IF(F$2:F$1255=I2,IF(G$2:G$1255<>J2,ROW(G$2:G$1255)-ROW(G$2)+1))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[James8761]

Thanks so much for this, Marcelo. It's great, does exactly what I want.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.