+ Reply to Thread
Results 1 to 3 of 3

Filters and Totaling by Segment

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    Filters and Totaling by Segment

    Hi all... so I have a spreadsheet where I am have filters in my columns, and at the bottom, a sum and average formula. When I select one of the filters (insted of all I select one of the segments), I lose my sum at the bottom -- it actually hides it -- but I also need to see the sum and average of just that filter. How do I do that?

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    MS-Off Ver
    office 97 ,2007

    Re: Filters and Totaling by Segment

    use subtotal function
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert
    Join Date
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home

    Re: Filters and Totaling by Segment


    Welcome to the Forum

    This sounds like you are using Data - Filter (not Advanced Filter)?

    If this is the case, it is likely that you need to remove the Filter from the data, select the range to be filtered (including the headers, but excluding the total row at the bottom of the page) & then apply the Filter. This should fix your problem which can arise when you only select the header row before applying the Filter. Personally, I would add an extra row or two above your headers & put the total and average into cells (using the Sub-Total function/formula as Martin has suggested) in these rows. This approach has the advantages of:
    - ensuring that the totals are never hidden by filters
    - is visible at a glance without scrolling (when you use Freeze Frames)
    - can be set to print on every page (if appropriate)
    - allows for easy data entry to the base of the spreadsheet because there are no totals in the way (note you have to take care of what range is used as an argument in the SubTotal function if data is added).

    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1