# Sumproducts

#### fifaclub

##### New Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Ganjin

##### Board Regular
dang it, just a minute xD

Last edited:

#### Ganjin

##### Board Regular

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))

Replies
0
Views
32
Replies
8
Views
92
Replies
18
Views
124
Replies
1
Views
87
Replies
10
Views
101