-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Sumif but only last 6 occurrences
- Thread starter SHARPY1
- Start date
Excel Facts
What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Please try to post the sample of this exhibit in an Excel readable form along with the desired result.
Upvote
0
| Team Home | Team Away | Goal Difference |
| A | G | 3 |
| B | F | 3 |
| C | A | 2 |
| D | B | 0 |
| E | C | 1 |
| G | A | 0 |
| A | D | 2 |
| B | A | 1 |
| C | G | 0 |
| D | G | 4 |
| E | F | 3 |
| F | E | 0 |
| G | C | 2 |
| A | E | 2 |
| B | E | 0 |
| C | F | 0 |
| D | C | 1 |
| E | V | 2 |
| F | A | 3 |
| G | D | 0 |
| A | G | 3 |
| B | F | 3 |
| C | A | 2 |
| D | B | 0 |
| E | C | 1 |
| C | F | 0 |
| C | A | 2 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
And this is 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)))
Again I had to replace the "," by the ";"
The formula retunrs the answer #NUM
Thank you for your help!
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
I get 4 as result, #NUM!.
This formula refers to A27 as a condition, a part of the data, which is not very wise.
Care to state what you want to calculate exactly?
Upvote
0
| Team Home | Team Away | Goal Difference | Date of Match | No of Match |
| A | G | 3 | 27.01.2014 | 27 |
| B | F | 3 | 26.01.2014 | 26 |
| C | A | 2 | 25.01.2014 | 25 |
| D | B | 0 | 24.01.2014 | 24 |
| E | C | 1 | 23.01.2014 | 23 |
| G | A | 0 | 22.01.2014 | 22 |
| A | D | 2 | 21.01.2014 | 21 |
| B | A | 1 | 20.01.2014 | 20 |
| C | G | 0 | 19.01.2014 | 19 |
| D | G | 4 | 18.01.2014 | 18 |
| E | F | 3 | 17.01.2014 | 17 |
| F | E | 0 | 16.01.2014 | 16 |
| G | C | 2 | 15.01.2014 | 15 |
| A | E | 2 | 14.01.2014 | 14 |
| B | E | 0 | 13.01.2014 | 13 |
| C | F | 0 | 12.01.2014 | 12 |
| D | C | 1 | 11.01.2014 | 11 |
| E | V | 2 | 10.01.2014 | 10 |
| F | A | 3 | 09.01.2014 | 9 |
| G | D | 0 | 08.01.2014 | 8 |
| A | G | 3 | 07.01.2014 | 7 |
| B | F | 3 | 06.01.2014 | 6 |
| C | A | 2 | 05.01.2014 | 5 |
| D | B | 0 | 04.01.2014 | 4 |
| E | C | 1 | 03.01.2014 | 3 |
| C | F | 0 | 02.01.2014 | 2 |
| C | A | 2 | 01.01.2014 | 1 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
The formula should return the total goal differences for the latest three games of a certain home team for a certain date. In this case of the home team which is in A27 = Team called "C". In example, for the date in bolt letters, the row with date 25.01.2014. I want to add an extra column in which I will have a trend for the three latest home games of team C. Thus this extram column should add, and only add, the last three Goal differences for home matches of team C (i.e. 0+0+2 = 2). This extra column I want to add for any row, thus the trend will be different for each team, depending on the date (and thus the result of the three home games before this date).
I need this information for a regression analysis as I want to control for the quality/trend for a team.
I hope I could make myself clear. Thank you again for your patience and help!
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
1) I do not understand why you insist on C being in A27 instead of having this criterion outside of the data area, say in E1.
2) The same holds for 25.01.2015. Why not have this criterion date outside the data area, say in F1?
3) You say "for a certain date", i.e., 25.01.2015. Then you go to indicate the C dates 19.01.2015, 12.01.2015, and 05.01.2015. What is the relation of these dates with the criterion date of 25.01.2015?
Upvote
0
1) I don't insist on this field. I just took it over from this thread, doesn't matter in which field it is. F1 is totally fine^^
2) Same here
3) The relation of these dates is that those dates represent the last three home games of Team C. They acctually do not really matter and should not be important for the formula. It was just a sidenote helping to understand what I am looking for. I highlightet the last three home games in red in the list above.
Upvote
0
Here my test file: Forum Test.xlsx (0.01 MegaByte) , If someone would like to fill in directly the corret formula. Please feel free to do so 
Otherwise a link with a screenshot of my improved formula and how it looks like: Pic-Upload.de - image.png
{=IF(COUNTIF(A3:A49,A2)>0,SUM(IF((A3:A49=A2)*(COUNTIF(A2,A3:A49)*(100-ROW(A3:$A49))>=LARGE(COUNTIF(A2,A3:A49)*(100-ROW(A3:$A49)),MIN(COUNTIF(A3:A49,A2),3))),C3:C49)),"")}
Otherwise a link with a screenshot of my improved formula and how it looks like: Pic-Upload.de - image.png
{=IF(COUNTIF(A3:A49,A2)>0,SUM(IF((A3:A49=A2)*(COUNTIF(A2,A3:A49)*(100-ROW(A3:$A49))>=LARGE(COUNTIF(A2,A3:A49)*(100-ROW(A3:$A49)),MIN(COUNTIF(A3:A49,A2),3))),C3:C49)),"")}
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Ok. Thanks.
~
| Row\Col | A | B | C | D | E | F | G |
1 | Team Home | Team Away | Goal Difference | Date of Match | Last | 3 | |
2 | A | G | 3 | 1/27/2014 | Team home | C | |
3 | B | F | 3 | 1/26/2014 | Total | 2 | |
4 | C | A | 2 | 1/25/2014 | |||
5 | D | B | 0 | 1/24/2014 | |||
6 | E | C | 1 | 1/23/2014 | |||
7 | G | A | 0 | 1/22/2014 | |||
8 | A | D | 2 | 1/21/2014 | |||
9 | B | A | 1 | 1/20/2014 | |||
10 | C | G | 0 | 1/19/2014 | |||
11 | D | G | 4 | 1/18/2014 | |||
12 | E | F | 3 | 1/17/2014 | |||
13 | F | E | 0 | 1/16/2014 | |||
14 | G | C | 2 | 1/15/2014 | |||
15 | A | E | 2 | 1/14/2014 | |||
16 | B | E | 0 | 1/13/2014 | |||
17 | C | F | 0 | 1/12/2014 | |||
18 | D | C | 1 | 1/11/2014 | |||
19 | E | V | 2 | 1/10/2014 | |||
20 | F | A | 3 | 1/9/2014 | |||
21 | G | D | 0 | 1/8/2014 | |||
22 | A | G | 3 | 1/7/2014 | |||
23 | B | F | 3 | 1/6/2014 | |||
24 | C | A | 2 | 1/5/2014 | |||
25 | D | B | 0 | 1/4/2014 | |||
26 | E | C | 1 | 1/3/2014 | |||
27 | C | F | 0 | 1/2/2014 |
G2, control+shift+enter, not just enter:
=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=G$2,ROW($C$2:$C$27)),MIN(G$1,COUNTIFS($A$2:$A$27,G$2))),IF($A$2:$A$27=G$2,$C$2:$C$27)))<strike></strike>
This sums the goal differences associated with the last three games of C as team home. Is this what you are after?
Upvote
0
Upvote
0
