Solved

# How can I adjust sum formulas to work when filtering?

Posted on 2011-10-03
188 Views
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
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

=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

LVL 50

Expert Comment

See example attached with some filtering

regards, barry
barry-training.xls
0

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.
=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

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.
0

Author Closing Comment

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

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.

#### Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!