Results 1 to 10 of 10

20071015, 21:28 #1
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20071015, 21:56 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: AutoFilter Formulas (MS Excel 2003)
Checkout John Walkenbach's site for Excel User Tip: Displaying AutoFilter criteria
Steve

20071015, 22:33 #3
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 criteriabut, using functions other than just SUBTOTAL. Is SUBTOTAL the only function that works this way.

20071015, 22:54 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20071016, 01:58 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: AutoFilter Formulas (MS Excel 2003)
Also see HOW TO: Use the SUBTOTAL Function with AutoFiltered Lists in Excel 2000 (applies to Excel 2003 too).

20071016, 21:54 #6
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 111 series that includes hidden values and the 101111 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?

20071016, 22:07 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20071016, 22:17 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20071017, 17:08 #9
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20071017, 17:54 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.