Last N entries thet meet criteria

Hello,
I have a worksheet with football results from the English premier league. The columns contain data on the home team, away team, goals scored by the home team and goals scored by the away team. I need a formula that sums the goals scored by a team in the last 5 matches (both home and away). For example, if Arsenal have played 10 games, I would like to know the total number of goals scored by Arsenal in matches 6,7,8,9 and 10, and not from game 1,2,3,4 or 5.
Thank You.

Assuming you enter Arsenal in H1, then to get Sum of last 5 Home games...

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER.

copy to next column cell to get last 5 away sum.

I came up with a non-array solution (I think) but requires two "dummy columns"
In G2 dragged down
=COUNTIF($C$2:$D2,C2)
In H2 dragged down
=COUNTIF($C$2:$D2,D2) You can hide these two columns if you like.

Then with a unique list of teams in J2:J21, In K2;
=SUMPRODUCT(--($C$2:$D$381=J2),--($G$2:$H$381>(COUNTIF($C$2:$D$381,J2)-5)), $E$2:$F$381)
See attachment

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

This gives me 11, if I have Arsenal in I1

Updated my spreadsheet with some test columns (I:K) and NBVC's formula. More times than not, the two are not agreeing. :/

It was a matter or interpretation.

My formula was calculated (probably wrongly) based on the team being the HOME team only (i.e I used column C only). Would need to adjust range for AWAY only.

My last formula accounts for columns C and D together and picking the last 5 games played by the choice team (Home or Away)

Even with the NBVC's new formula, the results don't match. Stoke for example only had 2 goals in the last 5 games by my calculations.

You are correct, I apologize... I will need to rethink the formula....

Okay, how about?

Please Login or Register  to view this content.

CSE confirmed.

in g2 dragged down to g1000
=IF(C2=$G$1,E2,IF(D2=$G$1,F2,""))
put team name in g1
in h1000 dragged UP to h2
=IF(G1000="","",IF(COUNTIF($G$1000:G1000,">="&0)<=5,G1000,""))
in h1 sum(h2:h1000)
there are only 760 premiere league games so from 1000 covers it easily

Okay, now the three of us (NBVC, Martin and I) are in agreement. The world is a beautiful place.

down to one helper now
g1 team name
h1 sum(h2:h1000)
h2 down
=IF(E2="","",IF(COUNTIF($C2:D$1000,$G$1)<=5,IF(C2=$G$1,E2,IF(D2=$G$1,F2,"")),""))
Its all good practice!