AutoFilter Formulas (MS Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Also see HOW TO: Use the SUBTOTAL Function with AutoFiltered Lists in Excel 2000 (applies to Excel 2003 too).

Reply | Quote