Also, don't forget that if you use 109 instead of 9 for the SUM, this will
ensure you will only get visible cells. so.. =SUBTOTAL(109,..)


"Stgeorge" wrote:

Thanks to all - finally got this figured out today. I actually took the
subtotal formula that it generated for me and extended it out over the entire
range rather than the one it gave me and it seems to adjusted for the filter.
Works very well.

Again thanks, I can stop scratching my rapidly balding head now.

"Pete_UK" wrote:

I discovered this in the dim and distant past, as I now put totals on
the top row. It seemed that Excel maintains an "active range" over
which the filters apply, and if you add anything to any cell in the
blank row below this, then the filter range gets extended.

Pete

Bob Phillips wrote:
"Pete_UK" wrote in message
ups.com...
Apply a filter to your data, then move to the bottom of the data. Leave
at least ONE blank row (ensure that the filter is not active for this
row, by checking the colour of the row indicator), and click the SUM
icon in the appropriate cell below this blank row - this will
automatically convert to SUBTOTAL(9, ...) and as it is outside the
range used by the filter it will always be available.

So there you go! I didn't know that. Can't see me ever using it, but at
least I know now <G