Current Streak for sports teams

Hello,

I currently have a spreadsheet which looks like this: (sample data)

I added helper columns (in yellow) to put a 1 in each respective result column which I thought might help with the process.

FbI1Jqv.png

I basically want to - per team - count the current streak, noting whether it is "Win", "Lose" or "Tie".

For example the current streak for Team A is "Win" for 2 weeks, for Team C it is "Tie" for 1 week.

This is the logic I'm thinking - I want something using formulae ideally, rather than VBA:

1) Find out what the latest result is for a team (e.g. "Win").
2) Using this result (e.g. "Win"), look down the table - it's populated with the latest date first, in team name order for each date [as above] - for whether the previous result for that team was a "Win" also. If it was not, the streak = 1. If it was, the streak = 2, or higher if there was another "Win" before that too (it will keep looking up until the value changes for that team).
3) Where a team does not play one week, this should not matter: it would just continue looking for a previous result for that team if there is one, and if it matches the current result.

Number 1 seems pretty straightforward but it's finding the current streak that I'm really struggling with! I was thinking something like this for the 'results' table (Team names can be manually entered):

qSvmXjF.png

Many thanks!!

Hi

Picture is no good to us as we can't see it!

Are you looking at current streak win/lose and ties?

it is just a win/ lose and ties or lookup each teams? what column?

Easy for you upload sample file for us to help you

Hi

I set up Team on column H
Win/Lose/Tie
I2

Formula:

Please Login or Register  to view this content.

This is array formula? Press same time Ctrl+Shift+Enter till you see both end like this{}, DO NOT PRESS ENTER? otherwise it won't work. Then copy down and cross.

See the file