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

 

[iliace]

What do you mean by "last occurence" - is that the largest number, or in the last row, etc?

 

[SHARPY1]

I have text in column E,
across the whole column, there are about 20 different texts
repeated throughout the column.

rather than sumif for each time a specified text appears,
i only want to sumif the last 6 occurrences of this text,
i.e. the previous 6

Cheers

 

[iliace]

You're not answering my question. What constitutes "last occurence"? How would you know, without a computer, by looking at the "texts" in column E, that it is one of the "last 6"?

 

[Jonmo1]

I think this may need a "Helper Column", say column J

In J2 put this formula..and fill down to J328
=COUNTIF(E$2:E2,E$329)>=(COUNTIF(E$2:E$328)-5)

That will give you TRUE/FALSE if it is one of the last 6 occurances.

Then you can SUMIF on That column
=SUMIF(J2:J328,TRUE,I2:I328)

 

[Jonmo1]

Ooops, almost had it.

formula for J2 would be

=AND(E2=E$329,COUNTIF(E$2:E2,E$329)>=(COUNTIF(E$2:E$328)-5))

 

[SHARPY1]

Team Name column A
Team name column B
Rating column C

i.e. in formula cell currently :SUMIF(C2:C26,C27,E2:E26)
however in that column there may be several entries matching cell c27,
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(C2:C26,C27,E2:E26)

 

[SHARPY1]

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)

 

[Jonmo1]

Did you try my suggestion?

 

[SHARPY1]

Yes mate, tried it but said 'Too few arguments' ??

Cheers