• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Subtotal Within a Filtered Table

L

Look

New Member
Why does putting a subtotal formula within an autofiltered table essentially break it?

I've attached a file for an example. The 2 tables within the file have exactly the same filter applied, but show different results.

It seems whenever I add a column containing SUBTOTAL(COUNTA formulas, the filters on every other column will no longer work correctly. i.e. data which should be filtered out remains visible, and rows remain hidden after the filter is cleared.

I don't necessarily need a solution, I'm just really curious as to why this is happening.

Thanks in advance for any insight!
 

Attachments

Hello Look,

Just Remove Filter from DATA and then refilter header. Filter will be refresh after do this.


Zameer
 
JAMIR said:
Hello Look,

Just Remove Filter from DATA and then refilter header. Filter will be refresh after do this.


Zameer
Click to expand...
That does not work...In the example I attached try removing the filter then reapplying the same one. The filter just starts to include 1 more erroneous row each time you remove and reapply the filter. A bit bizarre.
 
When you filter a table with a Totals line, you want the lines above it filtered but not the Totals line, since you want to show the Totals even if it doesn't fit in the filter criteria.

When SUBTOTAL is the only formula you use in a table cell, Excel thinks it's a Totals line and remove it from the filter automatically to match this behaviour.

You can work around by adding +0 to the formula after SUBTOTAL, or any other similar formula like VALUE(SUBTOTAL), etc.
 
Last edited:
Haz said:
When you filter a table with a Totals line, you want the lines above it filtered but not the Totals line, since you want to show the Totals even if it doesn't fit in the filter criteria.

When SUBTOTAL is the only formula you use in a table cell, Excel thinks it's a Totals line and remove it from the filter automatically to match this behaviour.

You can work around by adding +0 to the formula after SUBTOTAL, or any other similar formula like VALUE(SUBTOTAL), etc.
Click to expand...
It worked! Thanks so much, I'll be sure to remember this trick.
 
You must log in or register to reply here.
Back
Top