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

 

[Jonmo1]

Yep, my bad.
In J2 (or any available column) and Fill down to 328
=AND(E2=E$329,COUNTIF(E$2:E2,E$329)>=(COUNTIF(E$2:E$328,E$329)-5))

Highlighted the part I missed..

Then your sumif formula uses column J
=SUMIF(J2:J328,TRUE,I2:I328)

P.S.
There's no such thing as a thread that is "Too Long"

 

[SHARPY1]

This would only work,
for the one entry in this case Wigan,
i want to do the same sumif for each cell (team)

Might be easier if i email you the spreadsheet,
as i think we've got our wires crossed??

 

[Jonmo1]

Then you would repeat the process for each team.
You can hide the Intermidary columns with the formula
=AND(E2=E$329,COUNTIF(E$2:E2,E$329)>=(COUNTIF(E$2:E$328,E$329)-5))
Modified for the correct ranges for each respective team.

Someone else can probably come up with a Single Array Formula to do it, but I couldn't figure it out..

 

[Aladin Akyurek]

A27 houses a team value, which is the condition the range in column A must meet, such as Wigan Athletic...

B27:

Control+shift+enter...

=SUM(IF(ISNUMBER(MATCH(ROW($A$2:$A$26),
    LARGE(IF($A$2:$A$26=A27,ROW($A$2:$A$26)),
      ROW(INDIRECT("1:"&C27))),0)),$C$2:$C$26))

C27:

=MIN(6,COUNTIF($A$2:$A$26,A27))

sorry, may have confused you with columns,
here is correct version

Team Name column A
Team name column B
Rating column C

i.e. in formula cell currently :SUMIF(a2:a26,a27,c2:c26)
however in that column there may be several entries matching cell a27,
in this case Wigan Athletic there are 8 previous entries in column A,
i only want to sumif the last 6

Wigan Athletic Bolton Wanderers 5
Fulham Everton 4
Manchester City Tottenham Hotspur 2
Wigan Athletic Newcastle United 5
Manchester United Bolton Wanderers 4
Tottenham Hotspur Chelsea 1
Reading Birmingham City 4
Wigan Athletic Derby County 2
Newcastle United Fulham 4
Bolton Wanderers Manchester City 4
Wigan Athletic Portsmouth 1
Aston Villa Sunderland 1
Everton West Ham United 1
Blackburn Rovers Wigan Athletic 0
Wigan Athletic Arsenal 2
Manchester United Liverpool 2
Wigan Athletic Arsenal 4
Manchester United Aston Villa 1
Wigan Athletic Blackburn Rovers 2
Derby County Fulham 1
Birmingham City Manchester City 1
Wigan Athletic West Ham United 0
Portsmouth Wigan Athletic 2
Liverpool Everton 4
Chelsea Middlesbrough 1
Wigan Athletic Newcastle United 1 SUMIF(a2:a26,a27,c2:c26)

 

[emtoucan]

Hi, sorry to resurect a 4 year old post but I've found the sumif formula above to be exactly what I needed but I'm stuck on how to turn this into a countif formula.

I have a spreadsheet almost identical to the above, where I want to sum the score for a team but only for the past 5 occurrances.

I would also like to count the number of clean sheets for the same 5 occurances but I'm struggling to convert the formula from a sumif to a countif = 0

Thanks
Ben

 

[Aladin Akyurek]

Hi, sorry to resurect a 4 year old post but I've found the sumif formula above to be exactly what I needed but I'm stuck on how to turn this into a countif formula.

I have a spreadsheet almost identical to the above, where I want to sum the score for a team but only for the past 5 occurrances.

I would also like to count the number of clean sheets for the same 5 occurances but I'm struggling to convert the formula from a sumif to a countif = 0

Thanks
Ben

Would you post the results you want to see for the exhibit used in this thread?

 

[emtoucan]

Hi,

For the exhibit used, the OP wanted the sum of the last 6 occurances of Wigan Athletic, therefore the result would have been 10;

Wigan Athletic Portsmouth 1
Wigan Athletic Arsenal 2
Wigan Athletic Arsenal 4
Wigan Athletic Blackburn Rovers 2
Wigan Athletic West Ham United 0
Wigan Athletic Newcastle United 1

I would like to count the number of clean sheets (0) in the same selection of 6 occurances, which in this case would be 1 (see above)

Ben

 

[Aladin Akyurek]

Hi,

For the exhibit used, the OP wanted the sum of the last 6 occurances of Wigan Athletic, therefore the result would have been 10;

Wigan Athletic Portsmouth 1
Wigan Athletic Arsenal 2
Wigan Athletic Arsenal 4
Wigan Athletic Blackburn Rovers 2
Wigan Athletic West Ham United 0
Wigan Athletic Newcastle United 1

I would like to count the number of clean sheets (0) in the same selection of 6 occurances, which in this case would be 1 (see above)

Ben

Looks like you want...

A27 houses a team value, which is the condition the range in column A must meet, such as Wigan Athletic...

B27, control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(ROW($A$2:$A$26),
    LARGE(IF($A$2:$A$26=A27,ROW($A$2:$A$26)),
      ROW(INDIRECT("1:"&C27))),0)),($C$2:$C$26=0)+0))

C27, just enter:

=MIN(6,COUNTIF($A$2:$A$26,A27))

Adjust to suit (6 --> 5, for example).

 

[emtoucan]

Hi,

Great, it worked perfectly.

Many thanks for the speedy response.

Ben

 

[Aladin Akyurek]

Hi,

Great, it worked perfectly.

Many thanks for the speedy response.

Ben

You are welcome. Thanks for providing feedback.