# Thread: AutoFilter Formulas (MS Excel 2003)

1. ## AutoFilter Formulas (MS Excel 2003)

I have placed different formulas in cells above a range of data. After setting AutoFilter on all of the columns, how do make the formulas calculate the data returned by the AutoFilter criteria that I choose in any given column?

thanks

2. ## Re: AutoFilter Formulas (MS Excel 2003)

Checkout John Walkenbach's site for Excel User Tip: Displaying AutoFilter criteria

Steve

3. ## Re: AutoFilter Formulas (MS Excel 2003)

Thank you for the John Walkenbach site tip. That tip seems to only display the what criteria the AutoFilter is set to. I am trying to use something like a SUBTOTAL function calculating rows arguments based on the filter criteria--but, using functions other than just SUBTOTAL. Is SUBTOTAL the only function that works this way.

4. ## Re: AutoFilter Formulas (MS Excel 2003)

I don't understand what you are after. The Function on teh Walkenbach site gives the criteria used in each filtered column. The Subtotal function gives the result of a particular statistical function (it can do: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP) on the data that is filtered.

What tyoe of "row argument" (I am not sure what you mean by this) are you trying to calculate?

Steve

6. ## Re: AutoFilter Formulas (MS Excel 2003)

Steve,
Thank you. Your method works. I guess I was the SUBTOTAL functions went beyond the 11 Function_num that is has. I noticed the 1-11 series that includes hidden values and the 101-111 series that ignores hidden values.

There were some other equations other than those 11 that I was hoping to be able to use with the SUBTOTAL.

Is it possible to create some kind of an array formula inside the SUBTOTAL function to expand the capabilities?

7. ## Re: AutoFilter Formulas (MS Excel 2003)

I think you could create a user defined function which would look through the rows and ignore the hidden ones. If you wanted to create an array function you would have to build the criterion within them, they would not automattically look at just the filtered data.

Steve

8. ## Re: AutoFilter Formulas (MS Excel 2003)

If you explain what kind of calculations you want to do, someone might come up with a more specific suggestion.

9. ## Re: AutoFilter Formulas (MS Excel 2003)

Hans,
I was trying to do MEDIAN and QUARTILE Functions within a SUBTOTAL function in support of box plots. Any help would be greatly appreciated.

thank

10. ## Re: AutoFilter Formulas (MS Excel 2003)

Unfortunately, you cannot use SpecialCells(xlCellTypeVisible) in VBA to retrieve the filtered cells, for cells hidden by the filter still count as visible (no idea why - a strange design quirk)

So you'll have to write your own custom Median and Quartile functions that apply the same criteria as the autofilter in their calculations. That looks very tedious to me.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•