How can I adjust sum formulas to work when filtering?

Posted on 2011-10-03
Last Modified: 2012-05-12
Hello again experts,
See attached example.  I have several summary formulas that don't work when I filter my data by supervisor.  Can you help?

Question by:garyrobbins
    LVL 50

    Accepted Solution

    Hello Gary,

    I'm not 100% clear which formulas you mean, Assuming you mean those in rows 25, 27 and 28 try these versions

    In L25 copied to N25


    In L27 copied to N27


    and in L28 copied to N28


    Those will all ignore filtered out rows,

    regards, barry
    LVL 50

    Expert Comment

    by:barry houdini
    See example attached with some filtering

    regards, barry
    LVL 80

    Assisted Solution

    These formulas work the same way as barryhoudini's, but are a little shorter because they reference row 9 and thereby eliminate the need for MIN. They should be placed in the three cells indicated on your worksheet, then copied across.
    LVL 80

    Expert Comment

    By way of explanation, the use of OFFSET inside SUBTOTAL is a way of tricking it to work inside an array formula.

    The OFFSET function returns an array of 11 cells, one at a time. SUBTOTAL then dutifully does the equivalent of COUNT on each of those cells. If the cell contains a date, then SUBTOTAL returns 1. If not, then it returns 0. If the cell is hidden by a filter, then SUBTOTAL also returns 0.

    Because SUBTOTAL is working inside an array formula, it can be used to determine both whether column L contains a date (versus NA) as well as whether it is hidden by a filter.

    Author Closing Comment


    I wouldn't have thought of putting the SUBTOTAL function inside a SUMPRODUCT...

    Thank you for the FAST replies.

    Hope you found my point assignment equitable.

    Thanks, Gary

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now