How To Calculate Running/Ongoing Value When More Data Is Added?

Assuming every team plays each match day, you can try these formulas:
In cell H22:
=SUMIFS(E:E,A:A,">"&A22-3,A:A,"<"&A22,C:C,C22)-SUMIFS(F:F,A:A,">"&A22-3,A:A,"<"&A22,C:C,C22)-SUMIFS(E:E,A:A,">"&A22-3,A:A,"<"&A22,D:D,C22)+SUMIFS(F:F,A:A,">"&A22-3,A:A,"<"&A22,D:D,C22)

Then you can copy it down. In cell I22, you can enter this formula:
=SUMIFS(E:E,A:A,">"&A22-3,A:A,"<"&A22,C:C,D22)-SUMIFS(F:F,A:A,">"&A22-3,A:A,"<"&A22,C:C,D22)-SUMIFS(E:E,A:A,">"&A22-3,A:A,"<"&A22,D:D,D22)+SUMIFS(F:F,A:A,">"&A22-3,A:A,"<"&A22,D:D,D22)
And again, copy it down.

I'm quite sure this is NOT the most efficient way to do this, so stay tuned for some nicer formulas, but since no one else has answered yet, and these work, i thought I'd post them.

Thanks for the reply Greg.

I will give it a go later when I have time after work. The only issue is I am certain some teams will miss a match day due to various reasons. They will all play the same amount of games in the season but postponements and rearranged games can play a part in not all 10 matches being played in the same week.

Well it works, Greg.

Thank you for taking the time to work this out for me.

What is the concern with teams that miss a match day?

Well, the formula goes back 2 "Match Day's". So if you are on Match Day #6, and the team didn't play Match Day#5, then it will only get the difference for Match Day #4. It doesn't go back to the last 2 matches the team played, it goes back 2 "Match Days". Not sure if that's an issue or not.

Ah I see. Yes that is a problem.

Rather then using formulas containing entire columns ( which is bad practice), you can use Excel's "Table" tool.
Each time you add a row, the formula adapts.
Explanations can be found, for example at https://www.contextures.com/xlExcelTable01.html

Yes a table is definitely the way to go here.

I was wondering - is it not possible to use the team name rather than the match day column (A) to find the teams and do the calculations?

So instead of calculating back 2 match days, we calculate back 2 team name occurrences? The issue is that the team name alternates from one column to the other depending on whether they are playing at home or away. But I feel this would solve the problem of missing a match day.

Any thoughts, anyone?

I think the answer lies in searching for the last 2 occurrences of a team name in either column and working out the sum of goals scored and conceded from those results.

So how do I search for the last 2 occurrences of the team?

Thanks for doing this work.

My first impression is that something is not right though. Bologna's goal difference in match day 3 should be 0 based on the previous 2 games where they won by 1 goal and lost by 1 goal. Their opponents, Fiorentina, should be on a GD of +1 (or simply 1).

Columns H and I represent the goal differences going into the match in question based on the information from the last 2 matches played.

Having looked at the formula, it is on the right path but not quite there.

What it needs to do is look at Bologna and see they won 3-2 two games ago and count that as a GD of 1, then find the next occurrence of Bologna and see that they lost 1-0 (when away) and count that as a GD of -1, then add both of those together and display the answer in H22 (answer should be 0)

I am only using 2 previous games to get the formula ready, the amount of games to consider will be much larger but thought it best to get the calculations right first.

The home one looks correct - as far as producing the correct result (tried it down a few teams) - but the away one is not playing well for some reason.

Just to answer your other point above, teams will not necessarily alternate from home and away, they could play consecutive games home or away and not sequentially. I want to capture any occurrence of a team playing.

Thanks so far - great work.

Amended the away formula to:

=IFERROR((LOOKUP(2,1/(C$1:C21=D22),E$1:E21)-LOOKUP(2,1/(C$1:C21=D22),F$1:F21))+(LOOKUP(2,1/(D$1:D21=D22),F$1:F21)-LOOKUP(2,1/(D$1:D21=D22),E$1:E21)),"-")

Seems to be working now. On my way from work, so I will give it a full bash soon and report back.

Still haven't fully tested yet but I will do when I'm home. My other concern though is the lookups are going to spot the first occurrence and no more so when I increase the number of matches which include multiple home and away and not sequential, I am going to hit a brick wall.

I think some sort of combination of Greg's effort (sumifs based on match day column) and yours Ali is the solution just waiting to be discovered!

The attached file works, as best as I can tell. Again, it is not the elegant solution, but it works. I haven't looked at the formula above to see if I can make it easier yet.

First, I put it in a table. Then I added two columns which count the number of times a team shows up up until that game.

Now it doesn't matter if they play every Match day or not. They can skip and it still works.

When I get a chance to look at the above formula, I will see if I can create a formula without having to add the two columns (although the 2 additional columns don't hurt anything and you could even hide those columns as they are automatically populated because of being in the table.

By the way, my solution does not care if the teams are playing home or away in any sequence (they can play 2 home games, then 3 away games and it should still work). (An example to test is for the GD for Carpi's 4 Match Day. Carpi plays as the Away team for both match day's 2 and 3, giving a GD of -2 for Match day 4.)

Just had a quick glance there Greg and it seems to be working. I will test it later for real but looks like excellent work.

The bigger challenge will be increasing the number of matches and allowing the goal difference to be calculated over 32 matches. But I figure this will be an easy adjustment based on your formula above.

Superb work Greg.

I've attached a version where at the top of the table, I have a place where you can enter any number you want as far as how many matches you'd like to go back.

So just change that number and all the calculations change appropriately.

Utterly superb work there Greg. I am going to give it a true test at the weekend. The overall aim is to use a rolling 32 matches to gather the required GD figure. This will present a further problem down the line when teams who were promoted from a division below or relegated from a division above (where applicable) have their GD calculated using games from a different division.

One step at a time though and from an idea that has been swimming around in my head for a long time, giant leaps have been taken with this forum's help.

Just out of interest, when you change the figure at the top to change the number of matches - looking at the results, I presume it still works out the figures for every number of matches below that figure? Otherwise, if I set the figure to 10, I was half expecting the GD figures to be 0 until 10 matches were reached.