+ Reply to Thread
Results 1 to 5 of 5

Subtotal a filtered range

  1. #1
    Registered User
    Join Date
    05-12-2011
    Location
    glasgow
    MS-Off Ver
    Excel 2003
    Posts
    9

    Subtotal a filtered range

    hi

    does anyone know how to subtotal a range that has been filtered? I am aware of the 109 function, but this only ignores cells that are hidden

    so lets say column A details - Jan, Feb, March, April
    column B has monthly totals

    I then filter A to show only Jan & Feb. If I subtotal column B the sum includes the value's for March and April?

    Any ideas?

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Subtotal a filtered range

    Hi MDarcey,

    If you use the normal SUBTOTAL functions (1-11) it will account for filtered lists and only subtotal the visible cells (at least that's how it works in 2007).

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Subtotal a filtered range

    Quote Originally Posted by mdarcey View Post
    I am aware of the 109 function, but this only ignores cells that are hidden
    No, that's not correct. This formula only totals those values not hidden by filtering

    =SUBTOTAL(9,A:A)

    If you change the 9 to 109 then rows hidden by filtering will still be excluded...additionally any rows hidden using the Hide command will also be ignored. If you never use Hide then either one should work
    Audere est facere

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Subtotal a filtered range

    Here's some info on the SUBTOTAL() function.

    http://www.excelfunctions.net/Excel-...-Function.html

  5. #5
    Registered User
    Join Date
    05-12-2011
    Location
    glasgow
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Subtotal a filtered range

    thanks guys,

    had a play about in work this morning and seen the different results.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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