SUMIF last [3] entries in a range rather than the entire range

[AlphaQuail]

Hi,

I'm looking to build a football data sheet on matches played with a column that adds up the last 3 match goals for the team in that row. I've only managed to get as far as adding up all goals for a team in a static date range using SUMIF. I imagine this is more complex and requires some kind of changing sum range that's relative to the cell I'm in.

I'm intermediate at best so any array formulas and VB script would preferred to be avoided if possible.

in the watered down example below, I'd like to add up all home team goals for Hereford in the last 3 matches, therefore excluding the game on the 10th (row 1) giving a total of 5. I'd like this formula to copy down from a13 to future matches and therefore update. So the next week's match will add goals for hereford from e13,e11,e8 but exclude e2, e5. Yes you've guessed it... I'm trying to work out recent team form to predict match outcomes for financial gain...

Any help welcome



<a href="http://s1282.photobucket.com/user/belated_phenom/media/excel1_zps385d2c50.jpg.html" target="_blank"><img src="http://i1282.photobucket.com/albums/a521/belated_phenom/excel1_zps385d2c50.jpg" border="0" alt=" photo excel1_zps385d2c50.jpg"/></a>

 

[fibonacci1101]

Please attache the sample workbook so I could help

 

[Ron Coderre]

1) Attaching a sample workbook is a lot more helpful than posting a picture.

2) Using your posted scenario
and
J1: a home team to analyze....Hereford
K1: number of latest home games to analyze for that team...3

This regular formula, copied down, returns the sum of Home Team goals for the last K1 home games

A2: =IF(COUNTIF(C$2:C2,$J$1)>=$K$1,SUMPRODUCT((ROW(E$2:E2)>=LARGE((C$2:C2=$J$1)*ROW(E$2:E2),3))*(C$2:C2=$J$1)*E$2:E2),"n/a")

(Note: if your regional separator is a semicolon, instead of a comma, please adjust that formula)

That formula only begins returning values when the minimum number of home games for that team have been played.

Is that something you can work with?

 

[AlphaQuail]

Hi All,

Apologies for not attaching the doc first time around, I'm new to the forum and actually didn't know I could attached hard files, hence hosting the image of the above and pasting in the link.

Ron, I've moved the cells around a little from the example you've given me and I have to say this delivers exactly what I was after. So thank you. And a quick response too, brilliant. If anyone wants me to add the doc for reference let me know and I'll do this when I get home from work,

Cheers

 

[AlphaQuail]

Hi all,

Hope everyone is well. I appear to have misunderstand what was required of this problem myself and thus asked everyone here for slightly the wrong thing.

I'm actually looking to work out, over the past X number of games a team has been involved in, how many goals has that team scored and had scored against them. I can attach a work book I'm just not sure how to do that. Can anyone help? Ron I think you had a firm grasp of this last time we spoke...

Thanks

Lee