SUBTOTAL and FILTER

C

confused

Hi,

I have a spreadsheet created by someone else. Autofilter is on all columns,
and there is a row at the bottom called TOTAL which has SUBTOTAL values in
it. When I filter, the TOTAL row is always displayed, which is what I want,
but I cant really understand why it is always displayed.
I actually want to add a second row which will always be displayed, but
beacuse I dont understand how it works, i'm not sure how to do this.

any ideas would be helpful

thanks!
 
C

CLR

It sounds like there is a macro coming in to play which re-places that
formula each time a new "filter" is effected. One way to do this without
macros, which I use a lot is to FREEZE the top 8 or 10 rows on the sheet and
put my SUBTOTAL formulas above that freezeline, that way they are always
visible no matter where I am looking at the filtered data.

hth
Vaya con Dios,
Chuck, CABGx3
 
C

confused

hi, thanks for your help but there is no macro!
the problem is that the total row calculates the total of all rows and if
you filter to show only some rows, the total row gives a new total. this is
the effect i want to see and freeze panes will not allow me to do this,

can anyone advise?

thanks
 
D

Debra Dalgleish

If you have a Subtotal formula in the last row of the table, that row is
excluded from the filter, and always remains visible.

If you want to add rows after the subtotal row, and have all these
summary rows remain visible, add a blank row between the table and the
summary rows.
 
C

CLR

If you put a SUBTOTAL formula above the FreezeLine, it will indeed calculate
only the filtered items in the specified column, and will remain visible
throughout the scroll of the data.

Vaya con Dios,
Chuck, CABGx3
 
B

broro183

Hi Confused - hopefully not for long!

With filters, the visible lines can be above the dropdown arrows of th
filters or below the selection that was filtered.
I'm assuming that your subtotals are at the bottom of your data, i
which case getting the subtotals to always show, is done by selectin
the range before applying the autofilter (nb actual filter, not jus
filter criteria).

To always show another row you may need to (shortcuts may wor
depending on version of Excel):
*Remove the autofilter eg [alt + d + f + f],
(My understanding is that if you insert the row while there is a
existing autofilter the new row would be incorporated into the filte
range therefore I remove it first.)
*Insert a row between your subtotal & data eg [alt + i + r],
*Select the range to filter excluding the subtotal row & the new ro
above it (depending on layout maybe able to use, [ctrl + home], [ctrl
shift + end], [shift + up arrow + up arrow])
*Replace autofilter eg [alt + d + f + f], and
* you should be away laughing!

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..
 
Top