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]

@ DeeEmmEss

You are welcome.

 

[markmzz]

Thanks also to Markmzz for taking the time to help - thankyou.

Hi DeeEmmEss,

You are welcome and thank you for the feedback.

I'm happy that your problem was solved. This is the most important thing.

Markmzz

 

[PedroGomez9]

Hi everyone,

Sorry to resurrect a 3 year old post (seems that's been a theme on this post since it was created 13 years ago!!)

I'm trying to do something, a little more simple than some of the previous posters but using what I suspect is a similar data set - I just can't figure it out!!

There's around 1500 rows of data, with 115 different teams featuring in the list around 10-15 times each at home, and a similar amount away. All I need to do is isolate the last 5 times each team name appears in the home column, and then the same for the away column with a true or false statement. I'm trying to build a picture of 'form' so need to use only the most recent 5 instances of data listed.

Selection of data listed below.

Thanks in advance!

Div	Date	HomeTeam	AwayTeam	Is last 5 home?	Is last 5 away?
FLC	11/09/2020	Watford	Middlesbrough
EPL	12/09/2020	Fulham	Arsenal
FLC	12/09/2020	Birmingham	Brentford
FLC	12/09/2020	Wycombe	Rotherham
EPL	12/09/2020	Crystal Palace	Southampton
FLC	12/09/2020	Barnsley	Luton
FLC	12/09/2020	Bournemouth	Blackburn
FLC	12/09/2020	Bristol City	Coventry
FLC	12/09/2020	Cardiff	Sheffield Weds
FLC	12/09/2020	Derby	Reading
FLC	12/09/2020	Huddersfield	Norwich
FLC	12/09/2020	Millwall	Stoke
FLC	12/09/2020	Preston	Swansea
FLC	12/09/2020	QPR	Nott'm Forest
FL1	12/09/2020	Accrington	Peterboro
FL1	12/09/2020	Crewe	Charlton
FL1	12/09/2020	Doncaster	Milton Keynes Dons
FL1	12/09/2020	Fleetwood Town	Burton
FL1	12/09/2020	Gillingham	Hull
FL1	12/09/2020	Lincoln	Oxford
FL1	12/09/2020	Northampton	AFC Wimbledon
FL1	12/09/2020	Plymouth	Blackpool
FL1	12/09/2020	Portsmouth	Shrewsbury
FL1	12/09/2020	Sunderland	Bristol Rvs
FL1	12/09/2020	Swindon	Rochdale
FL2	12/09/2020	Barrow	Stevenage
FL2	12/09/2020	Bolton	Forest Green
FL2	12/09/2020	Bradford	Colchester
FL2	12/09/2020	Cambridge	Carlisle
FL2	12/09/2020	Cheltenham	Morecambe
FL2	12/09/2020	Mansfield	Tranmere
FL2	12/09/2020	Oldham	Leyton Orient
FL2	12/09/2020	Port Vale	Crawley Town
FL2	12/09/2020	Salford	Exeter
FL2	12/09/2020	Scunthorpe	Newport County
FL2	12/09/2020	Southend	Harrogate
FL2	12/09/2020	Walsall	Grimsby
EPL	12/09/2020	Liverpool	Leeds
EPL	12/09/2020	West Ham	Newcastle
FL1	13/09/2020	Ipswich	Wigan
EPL	13/09/2020	West Brom	Leicester
EPL	13/09/2020	Tottenham	Everton
EPL	14/09/2020	Sheffield United	Wolves
EPL	14/09/2020	Brighton	Chelsea
FLC	18/09/2020	Coventry	QPR
EPL	19/09/2020	Everton	West Brom
FLC	19/09/2020	Nott'm Forest	Cardiff
FL2	19/09/2020	Carlisle	Southend
EPL	19/09/2020	Leeds	Fulham
FLC	19/09/2020	Blackburn	Wycombe
FLC	19/09/2020	Brentford	Huddersfield
FLC	19/09/2020	Luton	Derby
FLC	19/09/2020	Middlesbrough	Bournemouth
FLC	19/09/2020	Norwich	Preston
FLC	19/09/2020	Reading	Barnsley
FLC	19/09/2020	Rotherham	Millwall
FLC	19/09/2020	Sheffield Weds	Watford
FLC	19/09/2020	Swansea	Birmingham
FL1	19/09/2020	AFC Wimbledon	Plymouth
FL1	19/09/2020	Blackpool	Swindon
FL1	19/09/2020	Bristol Rvs	Ipswich
FL1	19/09/2020	Burton	Accrington
FL1	19/09/2020	Charlton	Doncaster
FL1	19/09/2020	Hull	Crewe
FL1	19/09/2020	Milton Keynes Dons	Lincoln
FL1	19/09/2020	Oxford	Sunderland
FL1	19/09/2020	Peterboro	Fleetwood Town
FL1	19/09/2020	Shrewsbury	Northampton
FL1	19/09/2020	Wigan	Gillingham
FL2	19/09/2020	Colchester	Bolton
FL2	19/09/2020	Crawley Town	Scunthorpe
FL2	19/09/2020	Exeter	Port Vale
FL2	19/09/2020	Forest Green	Bradford
FL2	19/09/2020	Grimsby	Salford
FL2	19/09/2020	Harrogate	Walsall
FL2	19/09/2020	Leyton Orient	Mansfield
FL2	19/09/2020	Morecambe	Cambridge
FL2	19/09/2020	Newport County	Barrow
FL2	19/09/2020	Stevenage	Oldham
FL2	19/09/2020	Tranmere	Cheltenham
EPL	19/09/2020	Man United	Crystal Palace
EPL	19/09/2020	Arsenal	West Ham
EPL	20/09/2020	Southampton	Tottenham
EPL	20/09/2020	Newcastle	Brighton
FLC	20/09/2020	Stoke	Bristol City
FL1	20/09/2020	Rochdale	Portsmouth
EPL	20/09/2020	Chelsea	Liverpool
EPL	20/09/2020	Leicester	Burnley
EPL	21/09/2020	Aston Villa	Sheffield United
EPL	21/09/2020	Wolves	Man City
FLC	25/09/2020	Huddersfield	Nott'm Forest
EPL	26/09/2020	Brighton	Man United
FLC	26/09/2020	Watford	Luton
FLC	26/09/2020	Wycombe	Swansea
EPL	26/09/2020	Crystal Palace	Everton
FLC	26/09/2020	Barnsley	Coventry
FLC	26/09/2020	Birmingham	Rotherham
FLC	26/09/2020	Cardiff	Reading
FLC	26/09/2020	Derby	Blackburn

 

[jasonb75]

Assuming the HomeTeam list is in column c, Something like this should give you the desired results for the home count, then change to column D for the away count.

=COUNTIF(C2:C$1500,C2)<=5

 

[PedroGomez9]

Hi Jason

Thanks for the quick reply - works perfectly. Here was me trying to massively overcomplicate things using OFFSET when there's such a simple solution!!

 

[jasonb75]

me trying to massively overcomplicate things

We've all done that

 

[PBingo]

In the grand tradition of this thread, I have a problem which is very similar to the question posed by DeeEmmEss in 2018 and answered successfully by Aladin Akyurek.

Although I am not using goals scored as the basis of my method, I can easily substitute my ratings once a solution has been found. The data shown is the same as the DeeEmmEss original except that the date order is reversed as that is how the spreadsheet would build over a season when I am adding new results. The two additional columns, I and J, are effectively the same figure that is produced by the solution formula box G3 in Aladin’s answer, which I did by amending the original formula slightly

However, although this allows me to produce new ratings per team for upcoming matches based on the last n games, what I can’t do is analyse previous season results because my amended formula produces the same result for every occurrence of a particular team’s name. So although it correctly calculates the last 6 match (home or away) goal total, it changes every previous figure for that team to the same total. Try as I might I can’t resolve this. For clarity I have added two more columns, L and M, showing what the correct figures should be.

Any assistance would be greatly appreciated

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	HomeTeam	AwayTeam	FTHG	FTAG		2		Total goals last n games from formula			Total goals last 2 games actual
2	31/01/2018	Chelsea	Bournemouth	0	3		Watford		1	3		0	3
3	31/01/2018	Everton	Leicester	2	1		2		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
I2:I4	I2	=SUM(IF(ROW($A$2:$A$59)>=LARGE(IF($B$2:$C$59=B$2,ROW($A$2:$A$59)),MIN($G$1,COUNTIFS($B$2:$C$59,B2))),IF($B$2:$C$59=B2,$D$2:$E$59)))
I5:J46,J2:J4	J2	=SUM(IF(ROW($A$2:$A$59)>=LARGE(IF($B$2:$C$59=C2,ROW($A$2:$A$59)),MIN($G$1,COUNTIFS($B$2:$C$59,C2))),IF($B$2:$C$59=C2,$D$2:$E$59)))
G3	G3	=SUM(IF(ROW($A$2:$A$59)>=LARGE(IF($B$2:$C$59=G$2,ROW($A$2:$A$59)),MIN($G$1,COUNTIFS($B$2:$C$59,G$2))),IF($B$2:$C$59=G$2,$D$2:$E$59)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

As this is a significantly different question from the op, please start a new thread of your own.
Also if you are trying to do it season by season it would help if you supplied more than one years data.

 

[PBingo]

As this is a significantly different question from the op, please start a new thread of your own.
Also if you are trying to do it season by season it would help if you supplied more than one years data.

 

[PBingo]

Thank you for your quick response. Happy to post as a new thread. Only posted here as one of the other participants had a very similar question although admittedly he wasn't the op. I can post more data if required although I am not sure how useful that would be. I will try to explain my aim a bit more clearly in the new post as the last attempt was a bit rambling!