Formula to count last 18 instances of occurrence (from a specific date) to perform a SUMIF

Hi,

I have historical football results from the last 15 years as per attachment. What I would like to do is take a specific row and have a formula that looks back at only the last 19 home games for a team so I can calculate how many goals have been scored in that game.

On attached, column A has date, C has the home team, D has the away team and I has the total goals scored in the game. So for example on row 4443 there is the fixture Liverpool vs. Arsenal so in column Y I'd like to be able to have a formula that looks back at only the last 19 home games that Liverpool have played before the date of this match (as per column A - 03/03/2012) and then adds up how many goals (column I) have been scored in those 19 games.

In column Z I'd also then like to be able to look at a similar thing but the last 19 away games that Liverpool (the home team in this match) have played before 03/03/2012 and do the same formula for total goals in those last 19 when Liverpool playing away. I would then copy these fomulas all the way down from row 3 to the end of the data so I have a continual rolling record for each upcoming game.

I am not too bad with excel and understand a large range of the formulas and logic but I'm afraid this is beyond me and would massively appreciate any help.

Thanks!

Any ideas? Completely stuck! Driving me crazy.

Originally Posted by CRMORE

Hi,

I have historical football results from the last 15 years as per attachment. What I would like to do is take a specific row and have a formula that looks back at only the last 19 home games for a team so I can calculate how many goals have been scored in that game.

On attached, column A has date, C has the home team, D has the away team and I has the total goals scored in the game. So for example on row 4443 there is the fixture Liverpool vs. Arsenal so in column Y I'd like to be able to have a formula that looks back at only the last 19 home games that Liverpool have played before the date of this match (as per column A - 03/03/2012) and then adds up how many goals (column I) have been scored in those 19 games.

In column Z I'd also then like to be able to look at a similar thing but the last 19 away games that Liverpool (the home team in this match) have played before 03/03/2012 and do the same formula for total goals in those last 19 when Liverpool playing away. I would then copy these fomulas all the way down from row 3 to the end of the data so I have a continual rolling record for each upcoming game.

I am not too bad with excel and understand a large range of the formulas and logic but I'm afraid this is beyond me and would massively appreciate any help.

Thanks!

column Y sum of goales when played as home

and goals computed in column AA when team played as away team