Hi,

I'm looking for a way to count the current consecutive wins for a team in a
two team competition.

I've tried using 'countif', but that doesn't quite seem to work for me. It
seems like it should be a relatively simple thing to do, but I just can't get
it.

My current spreadsheet has the 'winner' of an event listed in column D. It
will only ever be one of two teams (Team 1 or Team 2). How can I set up a
formula in a cell to count the current winning streak of a team.

Ideally, it would reset once a streak is broken (and a new one starts).

Hope all that makes sense.

Thanks,
Scott

Here is one way to do it...

=ABS(SUMPRODUCT(MAX(ROW(D$2:D$65535)*(D$2:D$65535= "Team1")))-SUMPRODUCT(MAX(ROW(D$2:D$65535)*(D$2:D$65535="Team 2"))))

Just substitute the actual names of Team1 and Team2 where I show them as
place holders. Also note that I assume the first row is a header row. if
that is not the case, then change all D$2 references to D$# where # is the
row number where your data starts at. You can also make this formula a
little more efficient by changing all the D$65535 references to D$### where
### is the row number of the highest row you ever expect data to be filled
in to.

Rick


"Scopar" wrote in message
...
Hi,

I'm looking for a way to count the current consecutive wins for a team in
a
two team competition.

I've tried using 'countif', but that doesn't quite seem to work for me.
It
seems like it should be a relatively simple thing to do, but I just can't
get
it.

My current spreadsheet has the 'winner' of an event listed in column D.
It
will only ever be one of two teams (Team 1 or Team 2). How can I set up a
formula in a cell to count the current winning streak of a team.

Ideally, it would reset once a streak is broken (and a new one starts).

Hope all that makes sense.

Thanks,
Scott

*Maybe* something like this:

D2: T1
D3: T1
D4: T2
D5: T1
D6: T1
D7: T1
D8: T1
D9: T2
D10: T2

Enter this formula in E2:

=IF(D3=D2,"",1)

Enter this formula in E3 and copy down to E10:

=IF(D4=D3,"",COUNTIF(D$2:D3,D3)-SUMIF(D$2:D2,D3,E$2:E2))

This will be the result:

..............D.....E....
1.........................
2..........T1..........
3..........T1....2...
4..........T2....1...
5..........T1........
6..........T1........
7..........T1........
8..........T1....4...
9..........T2.........
10........T2....2...


--
Biff
Microsoft Excel MVP


"Scopar" wrote in message
...
Hi,

I'm looking for a way to count the current consecutive wins for a team in
a
two team competition.

I've tried using 'countif', but that doesn't quite seem to work for me.
It
seems like it should be a relatively simple thing to do, but I just can't
get
it.

My current spreadsheet has the 'winner' of an event listed in column D.
It
will only ever be one of two teams (Team 1 or Team 2). How can I set up a
formula in a cell to count the current winning streak of a team.

Ideally, it would reset once a streak is broken (and a new one starts).

Hope all that makes sense.

Thanks,
Scott