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?

 

[CoulterM]

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

I dont think so - eg the filter selects years 2015,2016, 2019. A rows with date 2018 is hidden.

I tried copying and pasting the data into a new sheet and doing the same thing and this time the formula worked. So there's clearly something going on in the original. Could I see if I can share a visions of it to show you? Thanks so much for your help!

 

[Fluff]

Are you saying the two formula now give different results?

 

[CoulterM]

Are you saying the two formula now give different results?

They weren't - but I've just realised the rows weren't actually "hidden" but the result of ANOTHER filter being applied on a different column. ?‍This had been done by someone else without my knowledge and I didn't realise. Thanks for your help - working through the possibilities led me to finally seeing the issue and apologies for taking up your time.

 

[Fluff]

Glad you sorted it & thanks for the feedback.