Sumif but only last n occurrences

[PBingo]

Columns A-H represents a list of soccer results in date order, showing the full time home and away goals for the respective teams. Cell H2 allows a team to be selected whilst H1 inputs the number of games, counting up from the bottom that you want to sum the goals scored by the selected team in their last n games, either home or away. H3 is the calculation. In the example shown, Man City scored a total of 4 goals (1+3) in the last 2 games they played.

What I wanted was a way to have the value in H3 displayed for the home and away teams in each row, rather than having to type a single team selection in H2. I eventually managed adapt the formula from H3 to do that in columns J and K. Hence cell J45 shows the same 4 goal total from Man City’s last 2 games as cell H3. However, what appears to happen is that all of Man City’s entries in columns J and K change to the same value (4) when their latest match is added to the spreadsheet. Obviously this happens for all the teams not just Man City. The last value for a team (the one closest to the bottom of the sheet) is correct but all other instances will be wrong. For a comparison I have added columns M and N which show what the correct goal totals should be for each teams last 2 games progressing down the sheet in date order. The last 10 games in J-K match the totals in M-N.

Is it possible to change the formula in J-K so that when a new row of results is added it only updates the latest goal total for a particular team and not all previous values? I have tried many variations but with no success. Any assistance would be greatly appreciated.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	HomeTeam	AwayTeam	FTHG	FTAG		No. Of Games	2		Total goals last n games from formula			Total goals last 2 games actual
2	31/01/2018	Chelsea	Bournemouth	0	3		Selected Team	Brighton		1	3		0	3
3	31/01/2018	Everton	Leicester	2	1		Total Goals	6		1	2		2	1
4	31/01/2018	Man City	West Brom	3	0					4	1		3	0
5	31/01/2018	Newcastle	Burnley	1	1					2	3		1	1
6	31/01/2018	Southampton	Brighton	1	1					1	6		1	1
7	03/02/2018	Arsenal	Everton	5	1					1	1		5	1
8	03/02/2018	Bournemouth	Stoke	2	1					3	1		2	1
9	03/02/2018	Brighton	West Ham	3	1					6	2		3	1
10	03/02/2018	Burnley	Man City	1	1					3	4		2	4
11	03/02/2018	Leicester	Swansea	1	1					2	5		2	1
12	03/02/2018	Man United	Huddersfield	2	0					5	2		2	0
13	03/02/2018	West Brom	Southampton	2	3					1	1		2	4
14	04/02/2018	Crystal Palace	Newcastle	1	1					2	2		2	0
15	04/02/2018	Liverpool	Tottenham	2	2					6	3		2	2
16	05/02/2018	Watford	Chelsea	4	1					2	1		4	1
17	10/02/2018	Everton	Crystal Palace	3	1					1	2		4	2
18	10/02/2018	Man City	Leicester	5	1					4	2		6	2
19	10/02/2018	Stoke	Brighton	1	1					1	6		2	4
20	10/02/2018	Swansea	Burnley	1	0					5	3		2	1
21	10/02/2018	Tottenham	Arsenal	1	0					3	1		3	5
22	10/02/2018	West Ham	Watford	2	0					2	2		3	4
23	11/02/2018	Huddersfield	Bournemouth	4	1					2	3		4	3
24	11/02/2018	Newcastle	Man United	1	0					2	5		2	2
25	11/02/2018	Southampton	Liverpool	0	2					1	6		3	4
26	12/02/2018	Chelsea	West Brom	3	0					1	1		4	2
27	24/02/2018	Bournemouth	Newcastle	2	2					3	2		3	3
28	24/02/2018	Brighton	Swansea	4	1					6	5		5	2
29	24/02/2018	Burnley	Southampton	1	1					3	1		1	1
30	24/02/2018	Leicester	Stoke	1	1					2	1		2	2
31	24/02/2018	Liverpool	West Ham	4	1					6	2		6	3
32	24/02/2018	Watford	Everton	1	0					2	1		1	3
33	24/02/2018	West Brom	Huddersfield	1	2					1	2		1	6
34	25/02/2018	Crystal Palace	Tottenham	0	1					2	3		1	2
35	25/02/2018	Man United	Chelsea	2	1					5	1		2	4
36	01/03/2018	Arsenal	Man City	0	3					1	4		0	8
37	03/03/2018	Burnley	Everton	2	1					3	1		3	1
38	03/03/2018	Leicester	Bournemouth	1	1					2	3		2	3
39	03/03/2018	Liverpool	Newcastle	2	0					6	2		6	2
40	03/03/2018	Southampton	Stoke	0	0					1	1		1	1
41	03/03/2018	Swansea	West Ham	4	1					5	2		5	2
42	03/03/2018	Tottenham	Huddersfield	2	0					3	2		3	2
43	03/03/2018	Watford	West Brom	1	0					2	1		2	1
44	04/03/2018	Brighton	Arsenal	2	1					6	1		6	1
45	04/03/2018	Man City	Chelsea	1	0					4	1		4	1
46	05/03/2018	Crystal Palace	Man United	2	3					2	5		2	5
Sheet1

Cell Formulas
Range	Formula
J2:K46	J2	=SUM(IF(ROW($A$2:$A$46)>=LARGE(IF($B$2:$C$46=B2,ROW($A$2:$A$46)),MIN($H$1,COUNTIFS($B$2:$C$46,B2))),IF($B$2:$C$46=B2,$D$2:$E$46)))
H3	H3	=SUM(IF(ROW($A$2:$A$46)>=LARGE(IF($B$2:$C$46=H$2,ROW($A$2:$A$46)),MIN($H$1,COUNTIFS($B$2:$C$46,H$2))),IF($B$2:$C$46=H$2,$D$2:$E$46)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

How about

=SUM(IF(ROW($A$2:$A2)>=LARGE(IF($B$2:$C2=B2,ROW($A$2:$A2)),MIN($H$1,COUNTIFS($B$2:$C2,B2))),IF($B$2:$C2=B2,$D$2:$E2)))

 

[PBingo]

How about

=SUM(IF(ROW($A$2:$A2)>=LARGE(IF($B$2:$C2=B2,ROW($A$2:$A2)),MIN($H$1,COUNTIFS($B$2:$C2,B2))),IF($B$2:$C2=B2,$D$2:$E2)))

Thanks Fluff

Looks like I was close but no cigar whereas you were bang on the money! I probably would have been at it for another month without seeing that. I know what I am going to be doing this weekend now.

Once again, many thanks.

 

[Fluff]

Glad to help & thanks for the feedback.