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