Counting consecutive instances of highest value in a row

[DG_Silva]

I'm sorry in advanced if my explanation isn't very clear, but here goes!

I've got a grid of results from four teams who are scored on a weekly basis. So far, my calculation has the following which highlights how many times each team has won (listed at the top of each team):

=SUMPRODUCT(IF(A3:A58>0,(SUBTOTAL(4,OFFSET($A$3:$D$58,ROW($A$3:$D$58)-ROW(A$3),0,1))=A3:A58)+0))

(Not really relevant, but there you go...)

What I'm trying to do is to record the highest streak each team has had as the winner for the week. So the table looks something like this (the numbers refer to position, not score):

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

In theory, Team A should have 2 and Team B should have 3. The formula I have so far is:

=MAX(FREQUENCY(IF(A3:A53=MAX(A3:D53),ROW(A3:D53)),IF(A3:A53=0,ROW(A3:D53))))

However, I know this is wrong because it's only returning 1 and not 3. I can't use a definite value such as '1' because the values are below 0 (a percentage) and vary each time. Can anyone help with this please?

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]A[/td][td]B[/td][td]C[/td][td]D[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

4​

[/td][td]

3​

[/td][td]

2​

[/td][td]

1​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1​

[/td][td]

2​

[/td][td]

4​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

1​

[/td][td]

3​

[/td][td]

2​

[/td][td]

4​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

4​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

2​

[/td][td]

1​

[/td][td]

3​

[/td][td]

4​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

2​

[/td][td]

1​

[/td][td]

4​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

2​

[/td][td]

3​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet23[/td][/tr][/table]

Array formula in cell A10:
=SUM((IF(MIN($A$2:$D$8)=A2:A8,A2:A8,"A")=A3:A9)*1)+1

How to enter an array formula
1. Copy formula
2. Double click on cell A10
3. Paste formula
4. Press and hold CTRL + SHIFT
5. Press Enter