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]

Thanks Aladin,

It took me one year to gather the data... It will take one more to be able to use them effectively!!

Good night, once again your help was priceless. I don't think many would devote so much time for a stranger.


Hasta la vista!

 

[Aladin Akyurek]

Thanks Aladin,

It took me one year to gather the data... It will take one more to be able to use them effectively!!

Good night, once again your help was priceless. I don't think many would devote so much time for a stranger.


Hasta la vista!

You're very welcome.

 

[Part16]

Dear Aladin good morning again,

Food for thought once again...

I am now wondering if I could deactivate permanently the subtotal functions that I have in the first sheet and get the same outcome with a macro command, upon request.

In this sheet there are around 25 columns for each of the 20.000 rows and a header cell to each column where contains a subtotal function, calculating the average of the values for a current selection, when I apply a filter.

Because there are so many data to be calculated every time, excel is getting slower and slower so as to respond and thus I was wondering if there could be a solution where to get these averages (to the cells that already have this function) only when I push a button, for instance.

So I will have the flexibility to apply the filter as many times as I want and when I decide which is the right selection, to ask for these averages.

I use the subtotal value as I think that it is the only formula that can calculate values even when you apply a filter.

As usual, many thanks in advance.

 

[Aladin Akyurek]

Dear Aladin good morning again,

Food for thought once again...

I am now wondering if I could deactivate permanently the subtotal functions that I have in the first sheet and get the same outcome with a macro command, upon request.

In this sheet there are around 25 columns for each of the 20.000 rows and a header cell to each column where contains a subtotal function, calculating the average of the values for a current selection, when I apply a filter.

Because there are so many data to be calculated every time, excel is getting slower and slower so as to respond and thus I was wondering if there could be a solution where to get these averages (to the cells that already have this function) only when I push a button, for instance.

So I will have the flexibility to apply the filter as many times as I want and when I decide which is the right selection, to ask for these averages.

I use the subtotal value as I think that it is the only formula that can calculate values even when you apply a filter.

As usual, many thanks in advance.

1) Have a look at decisionmodels.com for tips to speed a spreadsheet.

2) In view of (1), try to avoid formuals with INDIRECT or OFFSET, the so-called volatile functions.