SUBTOTAL function always ignoring hidden rows

[CoulterM]

I am using the Subtotal function to find totals and averages on a spreadsheet.

There are some rows that have been filtered out using the drop down filter option and others hidden manually.

Whether I use SUBTOTAL(1, range) or SUBTOTAL (101, range) the result is the same

This happens with SUBTOTAL (9, range) and SUBTOTAL(109, range) too

Surely these should return different results - one including the manually hidden rows and the other no?

I am fairly inexperienced in Excel but worried as to why this is happening and if I am making an error.

Any advice?

 

[Fluff]

Hi & welcome to MrExcel.
They should give different results, unless the rows hidden manually would also be hidden by the filter.

 

[RoryA]

IMO it's a bug in Excel. It doesn't like the combination of hidden rows and filtered rows - clearing the filter will actually unhide manually hidden rows within the filtered data.

 

[CoulterM]

IMO it's a bug in Excel. It doesn't like the combination of hidden rows and filtered rows - clearing the filter will actually unhide manually hidden rows within the filtered data.

Thanks for your reply, That's what I thought it might be. But I tried clearing the filter and both formulae still ignore the manually hidden rows

 

[CoulterM]

Hi & welcome to MrExcel.
They should give different results, unless the rows hidden manually would also be hidden by the filter.

Thanks for your reply - not the rows hidden by the filter are not the same as those hidden manually (though there may be some overlap)

 

[CoulterM]

Just a thought. The filter is applied via a different column to the one I have then totalled (among to select a specific date range, then I am totally and averaging numbers that are recorded against the date range)

would that make a difference?

 

[Fluff]

But I tried clearing the filter and both formulae still ignore the manually hidden rows

As Rory has said, that will also un-hide the manually hidden rows.

 

[CoulterM]

Just a thought. The filter is applied via a different column to the one I have then totalled (among to select a specific date range, then I am totally and averaging numbers that are recorded against the date range)

would that make a difference?

Actually just tested this and still both results are the same

 

[CoulterM]

As Rory has said, that will also un-hide the manually hidden rows.

Thanks but removing the filter still leaves some rows manually hidden. And both versions of the Subtotal Formula (9 and 109) still produce the same result

 

[Fluff]

Thanks but removing the filter still leaves some rows manually hidden

That suggests that those rows are below the range the filter is looking at.