to sum 6 matches from a list and to count the goal difference.

[andyh42]

HI,

I would like to sum the goals difference by a team for their last 6 matches, both home and away.

The first figure will appear in cell G71 showing the last 6 games goal difference for Bradford and in cell H71 for the last 6 games goals difference for Colchester.

Each match has a Match No.

so the final result would be
Match No. Match Wk. Date Home Tm Away Tm h/gdiff a gdiff home away
70 7 14/9 Bradford Colchester 10 -1 2 2

and then copied for the rest of the matches that season for all teams.

Thank you.

[Special-K]

Before you go reporting on anything make sure your data is consistent.

Column C contains a mixture of dates that are numbers and text.

[andyh42]

that's why I've given each match a match number column A to use as a starting point. If you need the dates I will change them.

Regards

[JeteMc]

This proposed solution employs a helper column which may be moved and/or hidden for aesthetic purposes.
The formula that populates the helper column (difference) is: =ABS(H2-I2)
The array entered formula* that populates column M is:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.