Help: AutoFilter not 'hiding' data from other formulas

[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

 

[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

 

[Guest]

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

 

[Martin Rice]

Andrew

I would suggest that you use the SUMIF function to create bespoke formula
for your spreadsheet. My understanding is that autofilter only hides.

Martin

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Visit Eighty-Twenty Spreadsheet Automation for professional customised
spreadsheet development

http://homepage.ntlworld.com/martin.rice1/

 

[Guest]

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

 

[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.

 

[agbiggs]

Thanks very much -- this points me in the right direction!

Andrew