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

x
  • 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?

Gary
Training-Records-Planner-Master-.xls
0
garyrobbins
Asked:
garyrobbins
  • 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

=SUMPRODUCT((L10:L20>=$A$2)+0,SUBTOTAL(2,OFFSET(L10:L20,ROW(L10:L20)-MIN(ROW(L10:L20)),0,1)))

In L27 copied to N27

=SUMPRODUCT((L10:L20>=$A$2)*(L10:L20<=$A$3),SUBTOTAL(2,OFFSET(L10:L20,ROW(L10:L20)-MIN(ROW(L10:L20)),0,1)))

and in L28 copied to N28

=SUMPRODUCT((L10:L20<$A$2)+0,SUBTOTAL(2,OFFSET(L10:L20,ROW(L10:L20)-MIN(ROW(L10:L20)),0,1)))

Those will all ignore filtered out rows,

regards, barry
0
 
barry houdiniCommented:
See example attached with some filtering

regards, barry
barry-training.xls
0
 
byundtCommented:
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.
=SUMPRODUCT((L$10:L$20>=$A$2)*SUBTOTAL(2,OFFSET(L$9,ROW(L$10:L$20)-ROW(L$9),0,1,1)))
=SUMPRODUCT((L10:L20>=$A$2)*(L10:L20<=$A$3)*SUBTOTAL(2,OFFSET(L$9,ROW(L$10:L$20)-ROW(L$9),0,1,1)))
=SUMPRODUCT((L$10:L$20<$A$2)*SUBTOTAL(2,OFFSET(L$9,ROW(L$10:L$20)-ROW(L$9),0,1,1)))
0
 
byundtCommented:
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.
0
 
garyrobbinsAuthor Commented:
Excellent!

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
0

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