# Help in counting streaks

#### dungeon1976

##### New Member
Hello,
I have been working on a spreadsheet to follow my country’s national soccer league.
I want to find a way to count some different streaks for each team.
Here is a sample of what I have so far from A1:AI7:

 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Team 1 D L D Team 2 D L L Team 3 W W L Team 4 W W D Team 5 L L L Team 6 D L L

<tbody>
</tbody>

The numbers on the first row are the Matchdays, D stands for “Draw”, L stands for “Loss” and W stands for “Win”.
All the “blank” cells have a formula that automatically fills each cell with the correct value when the game is played.
I have googled around and found some formulas that appear to be working, but others don’t.
 A AK AL AM AN AO AP 1 Current Streak Longest Winning Streak Longest Draw Streak Longest Losing Streak Longest No-win streak (D and L streak) Longest No-lose streak (D and W streak) 2 Team 1 1 D 0 1 1 3 1 3 Team 2 2 L 0 1 2 3 0 4 Team 3 1 L 2 0 1 1 0 5 Team 4 1 D 2 1 0 1 3 6 Team 5 3 L 0 0 3 3 0 7 Team 6 2 L 0 1 2 3 0

<tbody>
</tbody>

AK2: Current Streak
Code:
``{=IFERROR(LOOKUP(1E+100,FREQUENCY(IF(B2:AI2=LOOKUP(2,1/(B2:AI2<>""),B2:AI2), COLUMN(B2:AI2)),IF(B2:AI2<>LOOKUP(2,1/(B2:AI2<>""),B2:AI2),IF(B2:AI2<>"",COLUMN(B2:AI2)))))&" "&LOOKUP(2,1/(B2:AI2<>""),B2:AI2),"")}``

This one seems to be working.

AL2: Longest Winning Streak
Code:
``{=MAX(FREQUENCY(IF (\$B2:\$AI2="W",COLUMN(\$B2:\$AI2)),IF(\$B2:\$AI2<>"W",COLUMN(\$B2:\$AI2))))}``

This one seems to be working.

AM2: Longest Draw Streak
Code:
``{=MAX(FREQUENCY(IF(\$B2:\$AI2="D",COLUMN(\$B2:\$AI2)),IF(\$B2:\$AI2<>"D", COLUMN(\$B2:\$AI2))))}``

This one seems to be working.

AN2: Longest Losing Streak
Code:
``{=MAX(FREQUENCY(IF(\$B2:\$AI2="L",COLUMN(\$B2:\$AI2)),IF(\$B2:\$AI2<>"L",COLUMN(\$B2:\$AI2))))}``

This one seems to be working.

AO2: Longest No-win streak (D and L streak)
Code:
``{=MAX(FREQUENCY(IF(\$B2:\$AI2<>"W",COLUMN(\$B2:\$AI2)),IF(\$B2:\$AI2="W",COLUMN(\$B2:\$AI2))))-COUNTIF(\$B2:\$AI2,"")}``

This one seems to be working but I do not know for sure.

AP2: Longest No-lose streak (D and W streak)
Code:
``{=MAX(FREQUENCY(IF(\$B2:\$AI2<>"L",COL(\$B2:\$AI2)),IF(\$B2:\$AI2="L",COL(\$B2:\$AI2))))-COUNTIF(\$B2:\$AI2,"")}``

This is not working: AP4 should be 2 and not 0 but on AP5 it is correct. I do not know how to work this out. As all the range is selected I have used the COUNTIF formula to subtract the empty cells otherwise the result would be even stranger…

Would it be possible for you to check if is there anything wrong with these formulas (at least on the last I know there is).

I would like to do this without having to use VBA code.

All help would be appreciated.

Thank you.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi
Welcome to the board

Try in AP2:

=MAX(FREQUENCY(IF((B2:AI2="W")+(B2:AI2="D"),COLUMN(B2:AI2)),IF((B2:AI2<>"W")*(B2:AI2<>"D"),COLUMN(B2:AI2))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

Copy down

Hi
Welcome to the board

Try in AP2:

=MAX(FREQUENCY(IF((B2:AI2="W")+(B2:AI2="D"),COLUMN(B2:AI2)),IF((B2:AI2<>"W")*(B2:AI2<>"D"),COLUMN(B2:AI2))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

Copy down

Thank you for the welcoming.

I have tried the formula and it worked like a charm!!!

I have used it on AO2 and it looked like the formula I had was not working properly, so I am using it on AO column.

Have you checked the other formulas I have posted? Is there any way I can simplify them by using the same logic you have used in this one?

Thanks a lot for the help on this one. Please let me know about the others, if possible.

Last edited:
You can mark this thread as solved. This problem is solved with your help pgc01.

I'm glad it helped. Thanks for the feedback.

Replies
8
Views
330
Replies
3
Views
674
Replies
8
Views
2K
Replies
9
Views
227
Replies
2
Views
156

1,221,522
Messages
6,160,311
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back