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

 

[Rumci]

Hi,

I have quite similar issue to the mentioned above, but not exactly the same, so I hope anyone will be able to help me resolve this problem.

My database are the NCAA matches, with winner name in column A and loser in column B. I want to calculate the number of wins of team from column A in previous 5 matches, so the formula should take into consideration that team can appear either in column A or B.

I would be really grateful if anyone can help me with this problem.

 

[Aladin Akyurek]

@Rumci

Would you post a small sample along with the actual outcome?

 

[Rumci]

It's very simple database, looks like this.

WINNER LOSER

696	579
698	676
708	696
714	749
696	635
696	738
747	610
753	696
754	696
696	675

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

So in the last row I would like to have winning percentage of team 696 from previous 5 matches - it should be 0.4 in this case.

 

[markmzz]

Maybe this (for all question):

Layout

Team name	Team name	Rating		Result - 6	Result - 5	Result - shift 0	Result win % - 5 both col
Wigan Athletic	Bolton Wanderers	5		10	9	1	80%
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
******************	******************	*******	**	*********	*********	**************	***********************

<colgroup><col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;" span="2"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;" span="2"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <tbody> </tbody>

Formulas

Use Ctrl+Shif+Enter to enter the formulas

In E2

=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(6,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,$C$2:$C$27)))

In F2

=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(5,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,$C$2:$C$27)))

In G2

=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(5,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,IF($C$2:$C$27=0,1))))

In H2

=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF(($A$2:$A$27=$A$27)+($B$2:$B$27=$A$27),ROW($C$2:$C$27)),MIN(5,COUNTIF($A$2:$A$27,$A$27)+COUNTIF($B$2:$B$27,$A$27))),IF($A$2:$A$27=$A$27,1)))/5

Markmzz

 

[Aladin Akyurek]

It's very simple database, looks like this.

WINNER LOSER

696	579
698	676
708	696
714	749
696	635
696	738
747	610
753	696
754	696
696	675

<TBODY>
</TBODY>

So in the last row I would like to have winning percentage of team 696 from previous 5 matches - it should be 0.4 in this case.

Thanks for the clear sample and the actual outcome.

D1: 696 (a team of interest)

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

=SUM(IF(ISNUMBER(MATCH(ROW($A$2:$A$11),
    LARGE(IF($A$2:$A$11=D$1,ROW($A$2:$A$11)),
      ROW(INDIRECT("1:"&MIN(5,COUNTIF($A$2:$A$11,D$1))))),0)),1))/
 ROWS($A$2:$A$11)

 

[markmzz]

Hi Rumci,

Now with your data:

Layout

WINNER	LOSER
696	579
698	676
708	696
714	749
696	635
696	738
747	610
753	696	Last 5 games 696	Last 5 games 696	Last 5 games 696	Last 5 games 696	Last 5 games
754	696	Win	Win/last 5 games 696	Win/all games 696	Win/all games	Win 696/all games
696	675	3	0,60	0,43	0,30	0,40
********	*******	****************	*********************	******************	****************	******************

<tbody>
</tbody>

Formulas

In C11 - use Ctrl+Shift+Enter to enter the formula

=SUM(IF(ROW($B$2:$B$11)>=LARGE(IF(($A$2:$A$11=$A$11)+($B$2:$B$11=$A$11),ROW($B$2:$B$11)),
MIN(5,COUNTIF($A$2:$A$11,$A$11)+COUNTIF($B$2:$B$11,$A$11))),IF($A$2:$A$11=$A$11,1)))

In D11 - use Enter to enter the formula

=$C11/5

In E11 - use Enter to enter the formula

=$C11/(COUNTIF($A$2:$A$11,$A$11)+COUNTIF($B$2:$B$11,$A$11))

In F11 - use Enter to enter the formula

=$C11/ROWS($A$2:$A$11)

In G11 - use Ctrl+Shift+Enter to enter the formula

=SUM(IF(ROW($B$2:$B$11)>=LARGE(IF($A$2:$A$11=$A$11,ROW($B$2:$B$11)),MIN(5,COUNTIF($A$2:$A$11,$A$11))),IF($A$2:$A$11=$A$11,1)))/ROWS($A$2:$A$11)

I hope that the formulas above helps to solve your problem.

Markmzz

 

[Hubinho]

Hi Guys

Thanks to all the people who have tried to solve this issue. This thread was exactly what I was looking for. Although it has been more than a year since someone used this thread, I still hope to receive an answer

Unfortunately the provided solutions do not work for my excel 2007.
I wasn't really able to track the error, but in example for the code

=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(6,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,$C$2:$C$27)))

there is no solution, excel gives #VALUE?. If I try =IF($A$2:$A$27=$A$27,ROW($C$2:$C$27) also #VALUE?

Hopefully someone is able to help me.
Also if you have an other solution, this would be fine for me. I am only looking for an answer to the initial question in this thread, as I am trying to generate a trend for the quality of soccer teams.

Thank you guys!

 

[Aladin Akyurek]

Hi Guys

Thanks to all the people who have tried to solve this issue. This thread was exactly what I was looking for. Although it has been more than a year since someone used this thread, I still hope to receive an answer

Unfortunately the provided solutions do not work for my excel 2007.
I wasn't really able to track the error, but in example for the code

=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(6,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,$C$2:$C$27)))

there is no solution, excel gives #VALUE?. If I try =IF($A$2:$A$27=$A$27,ROW($C$2:$C$27) also #VALUE?

Hopefully someone is able to help me.
Also if you have an other solution, this would be fine for me. I am only looking for an answer to the initial question in this thread, as I am trying to generate a trend for the quality of soccer teams.

Thank you guys!

You need to confirm this formula with control+shift+enter. That means: Press down the control and the shift keys at the same time while you hit the enter key.

 

[Hubinho]

You need to confirm this formula with control+shift+enter. That means: Press down the control and the shift keys at the same time while you hit the enter key.

Thank you Aladin

I did this for sure, but I get now a different response --> #NAME?

Can't imagine what's the problem.

Thanks again for any help!

 

[Hubinho]

You need to confirm this formula with control+shift+enter. That means: Press down the control and the shift keys at the same time while you hit the enter key.

Thank you Aladin

I did this for sure, but I get now a different response --> #NAME?

Can't imagine what's the problem.

Thanks again for any help!