Error in filtering table - additional rows shown, rows hidden when filter removed

I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.

When I start using filters, I run into problems:

Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!


Can anyone out there help me understand what is happening and what should I do?

Thank you for your support.Attachment 301726

It looks like, but why? :-)
I need that formula for my Median calculation.

Thanks for looking at the issue

its to do with having subtotal in the last row
http://blog.contextures.com/archives...el-autofilter/
why are you using subtotal like that?
anyway
apparently the work around is to trick excel into not noticing its a subtotal() row
change formula in col h
to

=--SUBTOTAL(103,Reports!$A2)

Martin
This solved my problem. Thank you.

I need to do a Median calculation and for that I filter and then compute the median for the visible rows only.