Sumproducts

[fifaclub]

Hi! I will very much appreciate any help on this

I have a list of matches in a season in Premiership, the list has four columns - Home Team (A), Away Team (B), Home Team Goals (C), Away Team Goals (D).

I want to add a column next to each game that sums the goals scored in the previous five matches by the host team, no matter if the team played home or away.

If the current match is in Row 55 Using Sumproduct I reached the following formula "=SUMPRODUCT(($A$2:54=A55)*($C$2:C54)+(($B$2:B54=A55)*($D$2:D54)))" which gives me the sum of the goals in all previous matches. My question is how can I limit this to the last five games only.

 

[Ganjin]

dang it, just a minute xD
made a mistake

 

[Ganjin]

hehe, sorry about that

okay, try this:
=SUMPRODUCT(
(OFFSET($A$2,LARGE(IF(($A$2:A54=A55)+($B$2:B54=A55),ROW($A$2:A54),FALSE),5)-2,0):A54=A55)
*(OFFSET($C$2,LARGE(IF(($A$2:A54=A55)+($B$2:B54=A55),ROW($A$2:A54),FALSE),5)-2,0):C54)
+(OFFSET($B$2,LARGE(IF(($A$2:A54=A55)+($B$2:B54=A55),ROW($A$2:A54),FALSE),5)-2,0):B54=A55)
*(OFFSET($D$2,LARGE(IF(($A$2:A54=A55)+($B$2:B54=A55),ROW($A$2:A54),FALSE),5)-2,0):D54))
Ctrl+Shift+Enter

or, if you would prefer to use two columns:

Cell E55:
=LARGE(IF(($A$2:A54=A55)+($B$2:B54=A55),ROW($A$2:A54),FALSE),5)
Ctrl+Shift+Enter

Cell F55:
=SUMPRODUCT(
(OFFSET($A$2,E55-2,0):A54=A55)
*(OFFSET($C$2,E55-2,0):C54)
+(OFFSET($B$2,E55-2,0):B54=A55)
*(OFFSET($D$2,E55-2,0):D54))