Results 1 to 6 of 6
  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Auto Sum with Filter (Excel '03 & '07)

    I have a spreadsheet with several columns. I am using Filtering to sort out the columns either by rep name, date of activity, or amount of production and or several other factors. What I want to do is to have the columns auto sum the amount of production, and then after I filter them auto sum again to that particular filter. Is that possible?
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  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: Auto Sum with Filter (Excel '03 & '07)

    The SUBTOTAL function can calculate the statistical functions on filtered data. You can read about it in the HELP

    [I find putting some of the stats above the header row and then freezing the panes at the header row works well. You can even include the normal functions to calculate the stats for the entire set so you can compare them]

    Steve

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

    Re: Auto Sum with Filter (Excel '03 & '07)

    Let's say the amount of production is in column H, and that you'll have fewer than 1,000 data rows.

    The formula =SUM(H2:H1000) will sum *all* amounts in column H.

    The formula =SUBTOTAL(9,H2:H1000) will sum the visible amounts in column H, i.e. if you apply a filter, the formula will only sum the filtered items.

  4. #4
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Auto Sum with Filter (Excel '03 & '07)

    Sorry Steve,
    I went to the help in '07 and now I am confused even more.
    Edited: I figured it out. On my trial sheet I entered the formula =SUBTOTAL(109,h2:H20), I then ran a ZIp Code filter, and the sum totaled correctly for just those Zip Code.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  5. #5
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Auto Sum with Filter (Excel '03 & '07)

    Thanks Hans, I just edited my reply to Steve letting him know that I had figured it out. I used 109 so that the hidden values wont be calculated just the visible ones.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Auto Sum with Filter (Excel '03 & '07)

    Just to clarify, using either 9 or 109 as the first argument will not include rows hidden by the filter. The difference between the two is that 109 will ignore manually hidden rows too, whereas 9 will include them.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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