Sumif but only last 6 occurrences

[SHARPY1]

I have this sumif function,
but this will sum about 50 occurrences of data in E329,
how do i adapt formula to just sumif last 6 occurrences??

=SUMIF(E2:E328,E329,I2:I328)

Any help appreciated
Cheers

 

[Aladin Akyurek]

Thank you so much Aladin Akyurek!!
This was exactly what I was lookig for!!

You are welcome. Thanks for the update.

 

[DeeEmmEss]

My request is very similar to others in this thread. I have five columns with Date, Home Team, Away Team, Home Goals, Away Goals, and I wish to sum the last six occurrences of Team x - regardless of whether it is the Home Team or the Away Team. Therefore I wish to sum for example, Crystal Palace goals scored whether they played at home or away.

Date	HomeTeam	AwayTeam	FTHG	FTAG
05/03/2018	Crystal Palace	Man United	2	3
04/03/2018	Brighton	Arsenal	2	1
04/03/2018	Man City	Chelsea	1	0
03/03/2018	Burnley	Everton	2	1
03/03/2018	Leicester	Bournemouth	1	1
03/03/2018	Liverpool	Newcastle	2	0
03/03/2018	Southampton	Stoke	0	0
03/03/2018	Swansea	West Ham	4	1
03/03/2018	Tottenham	Huddersfield	2	0
03/03/2018	Watford	West Brom	1	0
01/03/2018	Arsenal	Man City	0	3
25/02/2018	Crystal Palace	Tottenham	0	1
25/02/2018	Man United	Chelsea	2	1
24/02/2018	Bournemouth	Newcastle	2	2
24/02/2018	Brighton	Swansea	4	1
24/02/2018	Burnley	Southampton	1	1
24/02/2018	Leicester	Stoke	1	1
24/02/2018	Liverpool	West Ham	4	1
24/02/2018	Watford	Everton	1	0
24/02/2018	West Brom	Huddersfield	1	2
12/02/2018	Chelsea	West Brom	3	0
11/02/2018	Huddersfield	Bournemouth	4	1
11/02/2018	Newcastle	Man United	1	0
11/02/2018	Southampton	Liverpool	0	2
10/02/2018	Everton	Crystal Palace	3	1
10/02/2018	Man City	Leicester	5	1
10/02/2018	Stoke	Brighton	1	1
10/02/2018	Swansea	Burnley	1	0
10/02/2018	Tottenham	Arsenal	1	0
10/02/2018	West Ham	Watford	2	0
05/02/2018	Watford	Chelsea	4	1
04/02/2018	Crystal Palace	Newcastle	1	1
04/02/2018	Liverpool	Tottenham	2	2
03/02/2018	Arsenal	Everton	5	1
03/02/2018	Bournemouth	Stoke	2	1
03/02/2018	Brighton	West Ham	3	1
03/02/2018	Burnley	Man City	1	1
03/02/2018	Leicester	Swansea	1	1
03/02/2018	Man United	Huddersfield	2	0
03/02/2018	West Brom	Southampton	2	3
31/01/2018	Chelsea	Bournemouth	0	3
31/01/2018	Everton	Leicester	2	1
31/01/2018	Man City	West Brom	3	0
31/01/2018	Newcastle	Burnley	1	1
31/01/2018	Southampton	Brighton	1	1

<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

I've tried the suggested formulas earlier in the thread but i'm not succeeding. Help please.

 

[Aladin Akyurek]

@ DeeEmmEss

What is the expected result for, say, Liverpool, considering the sample you posted?

 

[DeeEmmEss]

I would expect 10; (2+4+2+2)

 

[Aladin Akyurek]

I would expect 10; (2+4+2+2)

Control+shift+enter, not just enter:

=SUM(IF(ROW($A$2:$A$46)>=LARGE(IF($B$2:$C$46=G$2,ROW($A$2:$A$46)),MIN($G$1,COUNTIFS($B$2:$C$46,G$2))),IF($B$2:$C$46=G$2,$D$2:$E$46)))

where G2 = Liverpool and G1 = 6.

 

[DeeEmmEss]

Thanks for your prompt reply Aladin. However, I am getting a #NUM error with your solution. I have changed the column references as in my sheet I have Column C = Home Team, Column D= Away Team, Column E = Home Goals, Column F= Away Goals. I have Liverpool (or any other team) in K3 and 6 in K2. Perhaps you would be good enough to alter as per my above, but also explain certain parts of your formula. I am particularly confused over the first part where you have =SUM(IF(ROW(............. but use a column ($A$2:$A$46). I don't have many brain cells available for Excel formulas and those I have are confused,

 

[Aladin Akyurek]

Thanks for your prompt reply Aladin. However, I am getting a #NUM error with your solution. I have changed the column references as in my sheet I have Column C = Home Team, Column D= Away Team, Column E = Home Goals, Column F= Away Goals. I have Liverpool (or any other team) in K3 and 6 in K2. Perhaps you would be good enough to alter as per my above, but also explain certain parts of your formula. I am particularly confused over the first part where you have =SUM(IF(ROW(............. but use a column ($A$2:$A$46). I don't have many brain cells available for Excel formulas and those I have are confused,

Here is the workbook you can adjust to your own layout: https://www.dropbox.com/s/i4ehoiwhsqmdplv/DeeEmmEss%20only%20last%206%20occurrences.xlsx?dl=0

 

[markmzz]

Hi!

Try the formula below too:

=SUMPRODUCT((ROW($2:$46)>=LARGE(($C$2:$D$46=$K$3)*ROW($2:$46),
MIN(COUNT(INDEX(FIND(" "&$K$3&" "," "&$C$2:$D$46&" "),)),$K$2)))*($C$2:$D$46=$K$3)*$E$2:$F$46)

Markmzz

 

[Aladin Akyurek]

You don't any rewrite at all...

 

[DeeEmmEss]

Thankyou so much for your help Aladin, working perfectly now. Thanks also to Markmzz for taking the time to help - thankyou.