[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

How can I adjust sum formulas to work when filtering?

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?

  • 2
  • 2
2 Solutions
barry houdiniCommented:
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
barry houdiniCommented:
See example attached with some filtering

regards, barry
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.
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.
garyrobbinsAuthor Commented:

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now