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
garyrobbinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.