Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Missouri, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filters + calculations (Excel 2003)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    Greetings all,

    I have a question about excluding certain rows from an autofilter. In the attached spreadsheet, I'd like to be able to apply the autofilters using the arrow icons on the heading cells but still have the calculations visible at the bottom of each column. Seems like there should be a way to do this, or would I have to store my calculations on a separate worksheet?

    Ideally, the calculations would update to only consider the visible cells. Going by <post#=302082>post 302082</post#>, this would require some VBA. My concern is with some complex formulas I'm using in other columns (countif's with varying criteria, nested sum(if) functions, etc.) I think I can code the functions as in that post's examples, but I'm not sure how to pass the text criteria parts of my formulas. Could someone give me an example and show me the code I'd need for the formula in cell I28 of my sample spreadsheet?

    Any help you can give a rookie is appreciated.

  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: Filters + calculations (Excel 2003)

    If you add a row between the data and the "calcs" before you set the autofilter the info below the "break" will not be included.

    SUBTOTAL will calc the filtered data, for example:

    =SUBTOTAL(3,B2:B27)
    will do a "CountA" on the filtered cells

    SUBTOTAL will do standard "stat functions" (eg average, count, var, min, max, stdev). Other ones would have to be created as custom functions.

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Missouri, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters + calculations (Excel 2003)

    Thanks for the tip on the blank line. I'll give the subtotal function a try.

Posting Permissions

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