Which Excel functions ignore filtered rows? (Subtotal is one such function are there others?)

As far as I know, Subtotal is the only built in excel function that ignores filtered cells.

Are there any other such functions?

I will award points for examples of other functions, or for authoritative documentation stating that Subtotal is the only such function.

Also, as far as I know, there aren't any excel functions that ignore manually hidden cells.  Can anybody prove me wrong?
rberke
LVL 5
rberkeConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

byundtMechanical EngineerCommented:
AGGREGATE ignores hidden cells if you so choose (in the second parameter). If the second parameter is 1, 3, 5 or 7, then hidden rows are excluded, regardless of whether the rows were hidden by a filter or manually. It is available in Excel 2010 and later.

SUBTOTAL excludes manually hidden rows when the first parameter is 101 through 111. Manually hidden rows are included when the first parameter is 1 through 11.
byundtMechanical EngineerCommented:
I was guessing it might be otherwise, but the database functions (e.g. DAVERAGE, DCOUNT, DSUM) obey the filtering criteria in the function when deciding which rows to include. Even if rows are hidden by an AutoFilter, Advanced Filter or manually--if the database filtering criteria includes the rows, they are part of the answer.
byundtMechanical EngineerCommented:
If you want to exclude hidden rows from a function that would ordinarily include them, you can use an array formula with SUBTOTAL and OFFSET. SUBTOTAL doesn't normally work with array formulas, but it will grudgingly agree if you use OFFSET (and ROW) to pass a single cell at a time. Here is an example using SUMPRODUCT:
=SUMPRODUCT((A5:A12>5)*A5:A12*SUBTOTAL(103,OFFSET(A4,ROW(A5:A12)-ROW(A4),0)))

With the first parameter of 103, SUBTOTAL is excluding rows hidden either by filters or manually. If you change the first parameter in the SUBTOTAL to 3, then it excludes only rows hidden by filters.

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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

[ fanpages ]IT Services ConsultantCommented:
Also, as far as I know, there aren't any excel functions that ignore manually hidden cells.  Can anybody prove me wrong?

PS. The SUBTOTAL function numbers 101 & above ignore manually hidden rows too.

Coincidentally, it is the basis of a solution I have just proposed within this question thread:

[ http://www.experts-exchange.com/questions/28712893/How-do-I-dynamically-hide-fields-based-on-an-auto-filter-selection.html ]
rberkeConsultantAuthor Commented:
very good responses from both, but Brad's is exceptional.  

Also, I have discovered that mixing manually hidden and auto filtering can cause incorrect results.
In the attached spreadsheet, row 8 is manually hidden and row 11 is auto filtered.

So row 8's value should be included in  =SUBTOTAL(9,B7:B12) and should be excluded from subtotal(109,b7:b12).

But row 8 is incorrectly excluded from BOTH.

I'll take comments on this weirdness, then I will close the problem
hidden-and-filtered.xlsx
[ fanpages ]IT Services ConsultantCommented:
Prior to SUBTTOTAL() being available I used to loop through rows checking RowHeight = 0 (meaning "hidden").

I suspect this (still) works for both manually-hidden & autofilter-hidden rows.
byundtMechanical EngineerCommented:
In Excel 2007, 2010 & 2013, if I manually hide row 8 and then apply the AutoFilter to hide row 11, Excel tries to be helpful and undoes the manual hiding and replaces it with a deselected box on the AutoFilter. You can see this if you open the AutoFilter a second time. As a result, SUBTOTAL(9,B7:B12) should be expected to exclude B8 from the sum--which it does.

If I reverse the order and apply the AutoFilter first before manually hiding row 8, Excel isn't quite smart enough to deselect the box for row 8 on the AutoFilter--but SUBTOTAL(9, B7:B12) still excludes B8.

While this behavior may seem like a bug, I predict Microsoft will call it a "feature" and decline to do anything about it.

To summarize the behavior, if you hide rows manually and also apply a filter using AutoFilter, SUBTOTAL assumes that all rows are being hidden by the filter. If AutoFilter is on, but no filters are in effect, then SUBTOTAL knows how to handle manually hidden rows. If there is no AutoFilter, then SUBTOTAL knows how to handle manually hidden rows.
byundtMechanical EngineerCommented:
Microsoft Excel MVP Debra Dalgleish blogged about this particular SUBTOTAL weirdness about halfway down the page in this link: Subtotal Quirks
rberkeConsultantAuthor Commented:
I was hoping that I had discovered something new, but as usual Brad knows all about it and can cite a blog for 2011 that describes the precise problem.  You never cease to amaze me.

I agree it would be pointless to point it out the Microsoft, they have far bigger problems to worry about.

Here come the points and thanks
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.