Help: AutoFilter not 'hiding' data from other formulas

A

agbiggs

I'm using AutoFilter on a columns of personal data for which I've set
up other calculations (averages, percentiles, etc). I was hoping that
if I filtered, say, just for women, then the other calcualtions would
reflect only the women's data, but they still seem to reflect the whole
column of data, including data that's hidden by AutoFilter. Is there
any easy way to fix this? Thanks!

Andrew
 
P

Pete_UK

Do you mean that you have some SUM( ... ) formulae which do not change
to reflect only the displayed values? If so, you should change these to
SUBTOTAL(9, ... ).

Hope this helps.

Pete
 
C

CLR

Check the HELP for a good explanation of the SUBTOTAL formulas which work the
same as the regular SUM, AVERAGE, etc except they only use the visible
rows....

hth
Vaya con Dios,
Chuck, CABGx3
 
P

Patricia Shannon

When I looked up Help for Subtotal, it appears that you want to use 109 to
ignore hidden values.
 
D

Dave Peterson

Those 1## series will make =subtotal() ignore rows hidden manually (as well as
those hidden by the filter).

They were added in xl2003.
 
Top