Excel "Dynamic SUM" when Grouping / Hiding Data....?

Posted on 2011-10-10
Last Modified: 2012-05-12
I have columns of data that have a SUM TOTAL "ie..SUM(a10:a2000)"

I would like to filter on a column and then have this TOTAL SUM ONLY THE FILTERED ITEMS....

How do i do that.....?

As it stands now.,..when I filter the Total for ALL rows is still showing in the total cell. What syntax or code do I use in the total cell so that it will ONLY summarize the FILTERED data rows..?

Question by:MIKE
    LVL 50

    Accepted Solution

    Use SUBTOTAL function to sum with visible data only


    9 indicates SUM - there are other numbers for different functions like AVERAGE, MIN, MAX etc. see SUBTOTAL help for more....

    regards, barry
    LVL 6

    Assisted Solution

    it's called the subtotal function.
    the syntax is subtotal (#, range)
    where # is the magic number of what type of subtotal you want. Sum, average etc. The number for summing is 9.
    so if I wanted to sum a1:a10
    I could put in cell say a12
    =subtotal (9, a1:a10)
    then if i add a filter and end up hiding a few rows, that total in a12 will change accordingly.

    or option 2... you can add some auto filters to your data first. Then choose an option on the filter so that it ends up hiding some cells.
    Next at the bottom of the column you want to sum, click the sum button and select the first row to the end of the column (even though some rows are hidden)... Excel knows that you have hidden rows it will create a subtotal formula instead of a sum formula.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now