asked on

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

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

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.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Berke

ASKER

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 ]

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.

byundt

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.

byundt

Microsoft Excel MVP Debra Dalgleish blogged about this particular SUBTOTAL weirdness about halfway down the page in this link: Subtotal Quirks

Robert Berke

ASKER

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