Sumif but only for the last 5 results

[Mikk]

Hi guys,

Sorry if this has been covered but I didn't even know where to start with search terms.

I'm playing around with a 'Goals Superiority Prediction System' for football. As part of this you need to calculate the goals scored by a team minus the goals conceded over a set number of games (in this case 5).

Normally I could run a sumif if it wasn't for the requirement for it being in the last 5 games. I could do it by hand but with 20 teams each with 38 games a season over the last 5 seasons it could take a while. Can anybody point me in the right direction? I'm usually pretty good at figuring things out for myself once I've got a starting point.

I'm using these stats tables for data: http://www.football-data.co.uk/englandm.php

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Mikk]

Thanks for the advice NBVC. I've attached an example spreadsheet and tried to make it as simple as possible. There would obviously be more teams involved but I've reduced it to 2 main teams (Aston Villa and Liverpool) to highlight what I'm trying to do.

The match in row 2 would be the next fixture to be played, hence no scores have been input.

I'm trying to figure out an equation to automate the F2 and G2 cells. These cells add the last 5 games' goals scored by the team minus the last 5 games' goals conceded.

For clarity, the matches recorded in rows 13 and 14 fall outside the last 5 games and would not be included in the workings of F2 and G2.

Any help you can offer is greatly appreciated.

[Mikk]

OK, I've managed to figure myself a solution and I thought I'd share it in case anybody stumbles across a similar problem in future. I've attached the same spreadsheet example showing the solution.

The real problem is that I was over complicating it - you don't need to add the last 5, you need to keep a running total and remove the 6th historical match each time.

=IF(COUNTIF(B3:B$14,B2)<1,0,

VLOOKUP(B2,B3:F$14,5,FALSE)+VLOOKUP(B2,B3:F$14,3,FALSE)-VLOOKUP(B2,B3:F$14,4,FALSE)

-IF(COUNTIF(B3:B$14,B2)<6,0,

INDEX(B3:D$14,SMALL(IF(B3:D$14=B2,ROW(B3:D$14)-ROW(B3)+1,ROW(D$14)+1),6),3)-

INDEX(B3:E$14,SMALL(IF(B3:E$14=B2,ROW(B3:E$14)-ROW(B3)+1,ROW(E$14)+1),6),4)))

Line 1: If its the first game of that club goal difference must be 0

Line 2: (Previous Goal Difference) + (Previous Games Goals Scored) - (Previous Games Goals Conceded)

Line 3: If there are less than 6 previous games END, otherwise subtract line 4&5

Line 4 & 5: (6th Games Goals Scored) - (6th Games Goals Conceded)