+ Reply to Thread
Results 1 to 8 of 8

Filtering, Sorting and Then Sub-Totalling

  1. #1
    Registered User
    Join Date
    08-11-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Filtering, Sorting and Then Sub-Totalling

    I have a spreadsheet with 10 columns of data and approx. 5,000 records.

    I want to filter the records based on 2 of the columns - which I have done.

    Then I plan to sort the remaining records (based on the contents of a 3rd column).

    Finally, I want to sub-total the "Amount" column (Column #10), every time the contents of the 3rd (sorted) column changes.


    QUESTION: Is there an easy way to remove the filtered records so that my sub-totalling doesn't get derailed ?

    Right now, the hidden (filtered) records are causing problems with the sub-totalling.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Filtering, Sorting and Then Sub-Totalling

    Hi,

    You can use an =SUBTOTAL(9,your_range) to sum the total of all filtered rows in the range.

    It's difficult to comment further without understanding your data but it may be worth your while checking out the Pivot Table functionality excel gives you. This effectively combines filtering and totalling, and being a pivot table it is extremely easy to dice and slice it at the flick of a mouse.

    HTH
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filtering, Sorting and Then Sub-Totalling

    You need to use the functions 101-111 to exclude hidden rows in a filtered list:
    =SUBTOTAL(109, your-range)

    Functions 1-11 include hidden rows.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    08-11-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Filtering, Sorting and Then Sub-Totalling

    When I use the sub-total function, is there a way for me to specify that I would like to exclude the hidden rows ? The sub-total icon prompts me to answer just a few limited questions:

    At each change in _________
    Use function ____________
    Add sub-total to _______________

    Replace current sub-totals
    Page break between groups
    Summary below data


    So I'm a little unclear about where I can specify those special function numbers without having to do a lot of manual editing.
    Last edited by wilderfan; 08-11-2010 at 07:22 PM.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filtering, Sorting and Then Sub-Totalling

    See post #3.

  6. #6
    Registered User
    Join Date
    08-11-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Filtering, Sorting and Then Sub-Totalling

    The sub-total icon applies the formula =SUBTOTAL(9,range) multiple times to my column 10 (which is the amount column).

    So the solution is to replace the existing SUBTOTAL(9,range) with SUBTOTAL(109,range) for every cell in column 10 which contains a sub-total formula?

    I hope I can use the Find & Replace function for all the formulas at once. Otherwise, it's going to be a very tedious process converting all the formulas !
    Last edited by wilderfan; 08-11-2010 at 07:49 PM.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filtering, Sorting and Then Sub-Totalling

    Search/Replace will work fine.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Filtering, Sorting and Then Sub-Totalling

    Quote Originally Posted by JBeaucaire View Post
    You need to use the functions 101-111 to exclude hidden rows in a filtered list:
    =SUBTOTAL(109, your-range)

    Functions 1-11 include hidden rows.
    @JB - the above is not accurate - the 101+ functions are required if you are manually hiding rows as opposed to using Auto Filter
    (the 101+ functions are not available in versions prior to XL2003)

+ 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