sum of last N values

[munchy]

Hi there,

I have searched the forums for an answer to this but have not found this exact problem, hopefully someone can help!

I have a list of football results on a sheet (Prem) and I would like to interrogate to find the home goals scored of the last N games played by a particular team. I can sum the total goals scored by using:

=SUMIF(Prem!$B:$B,$F11,Prem!$C:$C)

where $F11 contains the name of the team in question:


date home away

1/8/07 Man U 3 0 Man C
1/8/07 Totten 2 1 Arsenal
1/8/07 West B 4 4 Villa
2/8/07 Arsenal 2 0 Leeds

etc... this list is quite long!

I would like to limit this sum to the last N entries (say 6 games). I have played about with arrays but to no avail!

Many thanks in advance!

[Paul]

Hi Munchy,

If you can use a helper column it might make it easier. For example:

Based on your example data in A5:E314, in F5 put the formula:

=COUNTIF($B$5:B5,B5)

Fill that formula down to F314. This will count the number of appearances of each team in the list (starting at 1 and incrementing up each time that team name appears again).

In G11 put the team you want to search for.
In G12 put the number of games you want to include in the sum.
In G13 use the formula shown below to get the correct sum:

=SUMPRODUCT(--(B5:B314=G11),--(F5:F314>=COUNTIF(B:B,G11)-(G12-1)),--(F5:F314<=COUNTIF(B:B,G11)),C5:C314)

Hopefully that will work for you. Obviously, G11 through G13 can be any cells you choose, just update the formula accordingly. Note that in SUMPRODUCT formulas the ranges used for comparison must be the same size, which is why I used B5:B314, F5:F314 and C5:C314. You also cannot use full columns in SUMPRODUCT formulas; although I did use full column references (B:B) in the COUNTIF formulas used for comparison, which is permitted.

[snasui]

Another way

List and sum data that you want

see attached file.

Hope this help.

[munchy]

Thank you both for replying, both excellent suggestions! I like the solution by pjoaquin as I can incorporate this into the league table.

I have an extension to this problem:

I would now like to find the goals scored and the goals conceeded by a particular team in their last N (say 6) games. I have altered the code by pjoaquin thus:

=COUNTIF($B$5:E5,$G$11)

So that it now increments when a team plays whether they play at home or away. The problem is the sum doesn't take into account whether the team is home or away, it just sums the 'home' column (or 'away' column).

For instance, the last 6 games (most recent at bottom) played by Villa were:

Villa 4 - 1 Newcastle
Reading 1 - 2 Villa
Arsenal 1 - 1 Villa
Villa 1 - 1 Middlesborough
Portsmouth 2 - 0 Villa
Villa 0 - 1 Sunderland

So the goals scored by Villa = 8
Goals conceeded by Villa = 7

I can only get it to sum the home or away columns (so the answer I get is goals scored 9, goals condeeded 6 which is clearly wrong!)

I would like to avoid having a 'list and sum' solution as I would like to add this to a column in a league table.

Thank you in advance, this really is a great help!

[snasui]

See example in attached file.

Hope this helps.