SUMIF function summing the last 6 rows

[Part16]

Good mornign to all,

I am trying to sum the last 6 rows in a continuously growing table when a specific name in a different column (letters column) is met.

In the below example I would like to sum lets say the last 2 entries when the letter in the first column is C.

I can get the last 6 rows results with the below function but I can not apply SUMIF or anything like it to filter the data when a criteria is met.

Any help is very much appreciated.

=SUM(OFFSET(A1;MATCH(1E+30;A:A)-1;0;-6;1))

[TABLE="width: 30"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Part16]

Many many thanks once again Aladin,

I will try it this evening and will give you feedback.

Have a nice day.

 

[Part16]

Hi Aladin,

It works great finally, thank you once again!

I have made an adjustment though so as to eliminate the E2 cell (the number of the games that have to be calculated) adding "6"&"5"&"4"&"3"&"2"&"1" in its place...

It seems to be working until now without filling the E2 cell... I don't know, we will see if it works in the future...


Please take a look below:

=AVERAGE(IF(ROW(Results!$H$4:$H$50000)>=LARGE(IF(Results!$H$4:$H$50000=Teams!B343;IF(ISNUMBER(Results!$CM$4:$CM$50000);ROW(Results!$H$4:$H$50000)));MIN("6"&"5"&"4"&"3"&"2"&"1";SUM(IF(Results!$H$4:$H$50000=Teams!B343;IF(ISNUMBER(Results!$CM$4:$CM$50000);1)))));IF(Results!$H$4:$H$50000=Teams!B343;Results!$CM$4:$CM$50000)))

A new issue arised though as the calculation takes a lot of resourses and when I use the filter it needs about 1-2 minutes to recalculate...

Probably a macro command where when I push a button will make the calculation and fill the cells only with the value and do it again when it is necessary...

Tough work for a man without basic knowledge of programming...

Thanks again!

 

[Part16]

"6" was enough finally.. not need for the rest of the numbers...

 

[Aladin Akyurek]

hi aladin,

it works great finally, thank you once again!

I have made an adjustment though so as to eliminate the e2 cell (the number of the games that have to be calculated) adding "6"&"5"&"4"&"3"&"2"&"1" in its place...

It seems to be working until now without filling the e2 cell... I don't know, we will see if it works in the future...


Please take a look below:

=average(if(row(results!$h$4:$h$50000)>=large(if(results!$h$4:$h$50000=teams!b343;if(isnumber(results!$cm$4:$cm$50000);row(results!$h$4:$h$50000)));min("6"&"5"&"4"&"3"&"2"&"1";sum(if(results!$h$4:$h$50000=teams!b343;if(isnumber(results!$cm$4:$cm$50000);1)))));if(results!$h$4:$h$50000=teams!b343;results!$cm$4:$cm$50000)))

a new issue arised though as the calculation takes a lot of resourses and when i use the filter it needs about 1-2 minutes to recalculate...

Probably a macro command where when i push a button will make the calculation and fill the cells only with the value and do it again when it is necessary...

Tough work for a man without basic knowledge of programming...

Thanks again!

1. Do you really need to have 50000 rows?

2. I don't understand the "6"&"5"&"4"&"3"&"2"&"1" bit in MIN?

 

[Part16]

1. I need 50.000 rows because I have 25.000 entries so far, adding ~500 new every week. Next year I am going to need a lot more and I don't want to exceed the limit without noticing and lead to miscalculations.

2. It was my "inspiration" to eliminate the cell E2. "6" is just fine. Rookie's games!

The file works great, but as I add everyday more and more functions it gets heavier and heavier and I will have to move to Access probably.

 

[Aladin Akyurek]

1. I need 50.000 rows because I have 25.000 entries so far, adding ~500 new every week. Next year I am going to need a lot more and I don't want to exceed the limit without noticing and lead to miscalculations.

2. It was my "inspiration" to eliminate the cell E2. "6" is just fine. Rookie's games!

The file works great, but as I add everyday more and more functions it gets heavier and heavier and I will have to move to Access probably.

Let's try the following.

Activate Formulas | Name Manager.
Activate the New tab.
Enter Lrow in the Name box.
Enter the following the formula (adapted to your European system)...

=MATCH(9,99999999999999E+307;Results!$CM:$CM)

Click OK.

Run the same procedeure in order to define...

Hrange as referring to:

=Results!$H$4:INDEX(Results!$H:$H;Lrow)

CMrange as referring to:

=Results!$CM$4:INDEX(Results!$CM:$CM;Lrow)

Now invoke the following self-adjusting (dynamic) formula...

Control+shift+enter, not just enter:

=AVERAGE(IF(ROW(Hrange)>=LARGE(IF(Hrange=Teams!B343;
  IF(ISNUMBER(CMrange);ROW(Hrange)));MIN(6;SUM(IF(Hrange=Teams!B343;
  IF(ISNUMBER(CMrange);1)))));IF(Hrange=Teams!B343;CMrange)))

Let's check whether the following tweak improves the performance a bit more...

=AVERAGE(IF(ROW(Hrange)>=LARGE(IF(Hrange=Teams!B343;
  IF(ISNUMBER(CMrange);ROW(Hrange)));MIN(6;COUNTIFS(Hrange;Teams!B343;
  CMrange,"<>"));IF(Hrange=Teams!B343;CMrange)))

 

[Part16]

Everything seems to work great with this function:


=AVERAGE(IF(ROW(Hrange)>=LARGE(IF(Hrange=Teams!B343; IF(ISNUMBER(CMrange);ROW(Hrange)));MIN(6;SUM(IF(Hrange=Teams!B343; IF(ISNUMBER(CMrange);1)))));IF(Hrange=Teams!B343;CMrange)))


But when I enter this one it says that "there are too many arguments for this function"

=AVERAGE(IF(ROW(Hrange)>=LARGE(IF(Hrange=Teams!B343; IF(ISNUMBER(CMrange);ROW(Hrange)));MIN(6;COUNTIFS(Hrange;Teams!B343; CMrange,"<>"));IF(Hrange=Teams!B343;CMrange)))</pre>

 

[Aladin Akyurek]

Everything seems to work great with this function:


=AVERAGE(IF(ROW(Hrange)>=LARGE(IF(Hrange=Teams!B343; IF(ISNUMBER(CMrange);ROW(Hrange)));MIN(6;SUM(IF(Hrange=Teams!B343; IF(ISNUMBER(CMrange);1)))));IF(Hrange=Teams!B343;CMrange)))


But when I enter this one it says that "there are too many arguments for this function"

=AVERAGE(IF(ROW(Hrange)>=LARGE(IF(Hrange=Teams!B343; IF(ISNUMBER(CMrange);ROW(Hrange)));MIN(6;COUNTIFS(Hrange;Teams!B343; CMrange,"<>"));IF(Hrange=Teams!B343;CMrange)))
</PRE>

I guess we missed a paren while substituting CountIfs(...) for Sum(if(...))...

=AVERAGE(IF(ROW(Hrange)>=LARGE(IF(Hrange=Teams!B343; 
  IF(ISNUMBER(CMrange);ROW(Hrange)));MIN(6;COUNTIFS(Hrange;Teams!B343; 
  CMrange,"<>")));IF(Hrange=Teams!B343;CMrange)))

 

[Part16]

Works perfect. I can't tell the difference though but it seems quick enough.

You see, I have around 900 teams and these functions must calculate every change, every time an additional match has been added.

I have left the functions (code) only to the first row and everytime I want fresh data I apply the formula to the rest 800 teams, copying and pasting with dropping down.

Then I copy the whole table and I paste only the values. It is the only way to be quick and flexible I assume...

At the "Results" sheet I have around 20 Subtotal functions calculating average values and results (1, X, or 2) so as to have statistical data every time I apply a filter so as to check only one team... So every time I apply the filter the system almost "crashes"

Do you think that this has to be converted to MS Access? I don't know much either but I believe that as the list is growing more and more, access could be a better solution...

 

[Aladin Akyurek]

Works perfect. I can't tell the difference though but it seems quick enough. [/code]

That's great.

You see, I have around 900 teams and these functions must calculate every change, every time an additional match has been added.

I have left the functions (code) only to the first row and everytime I want fresh data I apply the formula to the rest 800 teams, copying and pasting with dropping down.

Then I copy the whole table and I paste only the values. It is the only way to be quick and flexible I assume...

At the "Results" sheet I have around 20 Subtotal functions calculating average values and results (1, X, or 2) so as to have statistical data every time I apply a filter so as to check only one team... So every time I apply the filter the system almost "crashes"

Do you think that this has to be converted to MS Access? I don't know much either but I believe that as the list is growing more and more, access could be a better solution...

Yes, Access would be a good option. You'll need some development time though.