Excel 2007 : Count Consequtive Wins/Losses

**jld5571** · 11-15-2011, 03:23 PM

Help Needed

I would like to be able to have a formulated count for consequtive wins or losses for each person in a data range. Below is some sample data that I would use. I was thinking I needed to use something with Countif but at this point it's a little too complicated for me. What formula could I use to accomplish what I want? Your help is appreciated, Thanks

Jack

**daddylonglegs** · 11-15-2011, 03:28 PM

Hello Jack, to you want the biggest losing/winning streak?

If so this "array formula" will give the biggest losing streak for Jeff (3 in your example)

=MAX(FREQUENCY(IF(A$2:A$41="Jeff",IF(B$2:B$41="L",ROW(B$2:B$41))),IF(A$2:A$41="Jeff",IF(B$2:B$41="W",ROW(B$2:B$41)))))

confirmed with CTRL+SHIFT+ENTER

For winning streak swap the "W" with the "L"

**jld5571** · 11-15-2011, 03:45 PM

What I would like is to have the current streak for each person, whether it be a current win streak or losing streak and have those numbers displayed in cells D5:E5 for Jeff, D9:E9 for Jason, D13:E13 for Jack etc. Hope that helps

**daddylonglegs** · 11-15-2011, 03:51 PM

What does "current streak" mean? Where are the latest games, those at the top, those at the bottom?

**jld5571** · 11-15-2011, 03:53 PM

Sorry I didn't clarify, it'd be going from top (old) to bottom (new). I will have them in a table with current dates being added to the bottom. So for example the cell A2 would be 10/15/11 and cell A39 would be 11/10/11

**daddylonglegs** · 11-15-2011, 05:59 PM

OK try this formula for Jeff winning streak in D5

=IFERROR(IF(LOOKUP(2,1/(A$2:A$100=D3),B$2:B$100)="L","",COUNTIF(INDEX(A$2:A$100,IFERROR(MATCH(2,INDEX(1/(A$2:A$100=D3)/(B$2:B$100="L"),0)),0)+1):A$100,D3)),"")

In E5 use the same formula except change the two "L"s to "W"s. Only one at most will display a value