Counting Current Streaks in Data/Results (Conditional Counting Formula?)

Hi,

I posted a thread the other night to do with a particular worksheet that I was keen on trying to work out a formula to count a current win or loss streak. The example workbook I attached wasn't particularly great though (as the worksheet I was keen to put it in had a couple of VLOOKUPs on the main data sheet and a Solver Model in it).

I am still keen to try and figure out how to do this (ideally I'm looking almost for a countif that can be re-set if that make sense) and so I put together a College American Football Results sheet for 2011_2012 during my lunchbreak. The sheet contains all of the relevant games from the regular season and is a lot cleaner.

As you can see I have geared the sheet around column H the Home Team Margin of Victory (which is negative for a loss). This then governs a lot of other IF functions for putting in W's and L's or the winning and losing team names etc. In column N I have listed all of the teams, with COUNTIF's for wins and losses in Column's O and P. M and Q are games played (worked out in different ways).

All I am looking at doing is just working out what the current streak is (e.g. in the case of LSU 13 wins). I can see that ideally all I need is away of either being able to re-set the count in a COUNTIF almost (e.g. if it counted the number of wins in a list and then re-set itself on a loss to zero) or I'm thinking possibly a VLOOKUP?

If anyone could offer any pointers in the right direction this would be much appreciated.

Hi

I had a look. There some error on Total Wins and Total Losts.
O2 =COUNTIFS($I$2:$I$776,N2,$J$2:$J$776,"W")+COUNTIFS($L$2:$L$776,N2,$K$2:$K$776,"W")copy down.
P2 =COUNTIFS($I$2:$I$776,N2,$J$2:$J$776,"L")+COUNTIFS($L$2:$L$776,N2,$K$2:$K$776,"L")copy down
This will give you correct results. Countifs is faster than sumproduct.

You need to explain more clear? If you mean reset to 0. You need to clear all result to start again or new season. Yes it can be done vlookup or hlookup also index and match.

I do league stats every week.

Thanks for trying to help.

Can I switch the COUNTIFS for SUMPRODUCT as I am still using 2004 for Mac (I know COUNTIFS is 2007 onwards)?

Yeah I didn't think the re-set to zero comment through too clearly - all I was thinking is, if a team loses it would re-set the countif count to zero beyond the row where the loss occurred (e.g. in terms of UK football Man City's recent loss this week would give them a winning streak of zero). The easier way to do it I presume is to have a streak of whatever the most recent result is (e.g. in the Man City case it would be a losing streak of 1).

In the case of the attached what I was getting at is; is there a way of putting together a formula that would return a winning streak of 13 (either the number 13 or 13 W etc.) in a column against Louisiana State (who went undefeated in the regular season?).

Sorry didn't look what excel you on. No worry

Sumproduct
Total Wins P2 =SUMPRODUCT((B$2:B$1000=O2)*(C$2:C$1000>E$2:E$1000)+(F$2:F$1000=O2)*(D$2:D$1000>E$2:E$1000))
Total Losses O2 =SUMPRODUCT((B$2:B$1000=O2)*(C$2:C$1000<E$2:E$1000)+(F$2:F$1000=O2)*(D$2:D$1000<E$2:E$1000))

It will need more work on it. If you want winning streak? I have come up idea. I put in Rank on N2 =RANK(P2,$P$2:$P$1000)+SUMPRODUCT(($P$2:$P$1000=P2)*($S$2:$S$1000>S2))
Column S2 to W2 in light blue.
S2 I put number in 1 to 198
T2 =IF(ISNA(VLOOKUP(S2,N$2:R$1000,2,FALSE)),"",(VLOOKUP(S2,N$2:R$1000,2,FALSE)))
V2 =IF(ISNA(VLOOKUP(S2,N$2:R$1000,3,FALSE)),"",(VLOOKUP(S2,N$2:R$1000,3,FALSE)))
U2 =IF(ISNA(VLOOKUP(S2,N$2:R$1000,4,FALSE)),"",(VLOOKUP(S2,N$2:R$1000,4,FALSE)))
W2 =IF(ISNA(VLOOKUP(S2,N$2:R$1000,5,FALSE)),"",(VLOOKUP(S2,N$2:R$1000,5,FALSE)))

Also I put in Pick a Teams Y2. Click on Y3 and Pick a Teams. This will help you find the team name on I2 to L1000 and T2 to T200 in orange highlight.
Have a look attach file what I done.

Hi,

Thanks for having another go. I haven't figured out all of your formulas yet but some things are definitely off:

=COUNTIF($I$2:$I$776,N2) - has to provide total number of wins as I originally thought (e.g. count the number of times Air Force in this case appears in the winning team column and would apply to either mine or your workbooks)

I haven't figured out:

=SUMPRODUCT((B$2:B$1000=O2)*(C$2:C$1000>E$2:E$1000)+(F$2:F$1000=O2)*(D$2:D$1000>E$2:E$1000))

completely yet - but if you select them in Y3 (or just run a filter on the winning column - I on either sheet) you will see that they appear 7 times as per the result of my formula against the 4 from your formula. My N is your O because of the rank column added in.

The ranking workup in cells S to W is super efficient though (currently I just copy things onto a different worksheet and sort them by rank which I could put a Macro together to do I guess) and the Y2 and Y3 Pick Team and then highlight their results in I and L is clever (how did you do that? - I've looked and couldn't figure it out). It would be the easiest way of tracking a streak manually but I was still looking to automate the process though (via a formula if possible).

Hi,

Thanks for taking the time and sorry if I came across at all wrongly. Your P2 and Q2 now when copied down now match the results of my COUNTIFs (calculated a difference column to check) but I can see that because of the formulas (thanks for explaining them) that they will have more use going forward than a standard COUNTIF.

Re: the number of games in a season, in the case of College Football it was only 14. I just wanted to post a complete data set online, as if someone could help me, I wanted to be able to give them a complete picture (so it could be checked back and forth). I actually intend to use the formula in a different league but I just wanted to get the basics and how they were applied (and was it possible) before applying them to something else (if that makes sense).