Bug 60724 - SUBTOTAL() function doesn't skip auto-filtered rows
Summary: SUBTOTAL() function doesn't skip auto-filtered rows
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.16-dev
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-02-10 23:36 UTC by Greg Woolsey
Modified: 2019-03-18 19:28 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Greg Woolsey 2017-02-10 23:36:42 UTC
Per the Excel documentation:

https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939

In the "Remarks" section, both the all rows and "ignore hidden rows" versions of the totals functions should ignore all rows hidden by an auto-filter.  POI isn't doing that yet.

I see this empirically, with the additional behavior that if you manually hide a row that passes an auto-filter, it is also ignored by BOTH versions of the formula methods.

I don't even know where to start looking to figure out if a row is hidden or not in the formula evaluation flow. It will require investigating whether there are TwoDEvals in the operands we can use or if the Ptg array has anything we can use to look back at the row properties for a cell value, or if this needs to be handled at a higher level somewhere.

One of the sheets in a new test workbook I'm building for conditional formatting has this case as well, discovered by accident.  Once I check that in, I'll point to it here as a test case that can be used to verify a fix.
Comment 1 Greg Woolsey 2017-02-13 20:03:18 UTC
It appears there is no direct link in the persisted XML between a row marked hidden and a table AutoFilter.  This matches the observed Excel behavior, where manually hiding a row that passes filter still gets its value skipped by both versions of the function.

That actually makes the POI code simpler, as we don't have to evaluate filters, just know if a table has one applied or not when checking the hidden attribute of included rows.
Comment 2 Greg Woolsey 2019-03-18 19:28:20 UTC
r1855789 implemented the corresponding 'ignore hidden rows' versions of existing variations.  Still does not check for table filter applicability.