Finding the last five matches and sum up the goald

[Tresfjording]

Hi ...

I give up. My brain just don't get it.

How do I sum up the goals in Manchester Uniteds last five matches?

See attached workbook

I advance thanks.

[Ace_XL]

You need to factor in the header

=SUM(OFFSET(EPL!C:C,COUNTA(EPL!C:C)-5,0,5))

or you could use the INDEX function too

=SUM(INDEX(EPL!C:C,COUNTA(EPL!C:C)):INDEX(EPL!C:C,COUNTA(EPL!C:C)-4))

[Tresfjording]

No ... Not the last five matches of any club, but the last five matches of Manchester United. :-D

[Ace_XL]

Oh of course! Its home games that you're after since you suggest the result should be 12

Try

=SUMPRODUCT((EPL!$A$2:$A$210=A11)*(ROW(EPL!$A$2:$A$210)=LARGE((EPL!$A$2:$A$210=A11)*ROW(EPL!$A$2:$A$210),{1,2,3,4,5}))*EPL!$C$2:$C$210)




Note to self: Read instructions clearly!

[Tresfjording]

Hi again Ace_XL ...

The formula don't seem to work. All I get is an #N/A.

[Tresfjording]

I found the error ... Works fine now ... it seems ...
Thanks :-D