Sumproducts

fifaclub

New Member
Joined
Apr 5, 2011
Messages
1
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.
 

Some videos you may like

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
Joined
Jan 18, 2011
Messages
179
dang it, just a minute xD
made a mistake
 
Last edited:

Ganjin

Board Regular
Joined
Jan 18, 2011
Messages
179
hehe, sorry about that :biggrin:

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

Watch MrExcel Video

Forum statistics

Threads
1,108,709
Messages
5,524,433
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top