Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    3 Star Lounger
    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 criteria--but, using functions other than just SUBTOTAL. Is SUBTOTAL the only function that works this way.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AutoFilter Formulas (MS Excel 2003)


  6. #6
    3 Star Lounger
    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 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. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    Plutonium Lounger
    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.

  9. #9
    3 Star Lounger
    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

  10. #10
    Plutonium Lounger
    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.

Posting Permissions

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