Auto filter

[Shorty]

I have 350 rows of data and if I have excel filter down the rows of data
using Data/Filter/Autofilter. How do I get excel to add just the visible
columns of data and not to include everything else not filtered.



Thank You

 

[Norman Jones]

Hi Shorty,

Use the worksheet SubTotal function.

For example:

=SUBTOTAL(9,A1:A100)

See Excel help for details.

 

[Dave]

=SUBTOTAL(9,'your range') where 'your range' is the range you want to
filter on. You will have to put the formula in a different column if
you want to use a column range, otherwise the formula will refer to
itself. For example, in a test sheet, put rows of A's and B's in column
A. Enter random numbers beside them in column C. Enter =SUBTOTAL(9,B:B)
in C1. Watch the total in C1 as you filter the list. If you don't use a
column range, you might enter =SUBTOTAL(9,B2:B350) in B1. I usually put
such formulas at the top of the sheet, using freeze panes when the list
is not filtered. In your filtered list, the formula position will jump
around on the screen if you put it at the bottom of a filtered list.
For more help, search Excel on SUBTOTAL.