Bug 126505 - Wrong Subtotal of auto-filtered rows on opening XLSX (comment 6)
Summary: Wrong Subtotal of auto-filtered rows on opening XLSX (comment 6)
Status: CLOSED DUPLICATE of bug 99913
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
Keywords: bibisectRequest, filter:xlsx, regression
Depends on:
Blocks: Calculate Function-Subtotal XLSX-Autofilter
  Show dependency treegraph
Reported: 2019-07-22 10:57 UTC by Satit
Modified: 2020-02-20 15:58 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

Test file for bug of subtotal on XLSX (5.89 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-07-22 11:15 UTC, Satit

Note You need to log in before you can comment on or make changes to this bug.
Description Satit 2019-07-22 10:57:47 UTC
When open a Calc sheet (saved in xlsx format), the subtotal(9,range) with auto-filter rows display sum of all rows instead of filtered rows.
ITEMS 6000 <-- subtotal(B3:B10)
----- ----
AAAA  4000
XXXX  1000
XXXX  2000

Then do auto filter with only XXXX items. Calc display correctly as 3000
But after save(in XLSX)-close & reopen the sheet, it display like following:

ITEMS 6000 <= SUBTOTAL(9,B3:B10)
----  ----
XXXX  1000 
XXXX  2000

Steps to Reproduce:
1. ENTER ALL ROWS and subtotal as following:
ITEMS 6000 <-- subtotal(B3:B10)
----- ----
AAAA  4000
XXXX  1000
XXXX  2000

2. Create Auto-Filter on the above rows/columes
3. Choose XXXX of dropdown at ITEMS Filter (the subtotal still calculates correctly)
4. Save the sheet in xlsx format
5. Reopen the sheet, it will displays like following
ITEMS 6000
----  ----
XXXX  1000 
XXXX  2000

Actual Results:

Expected Results:

Reproducible: Always

User Profile Reset: No

OpenGL enabled: Yes

Additional Info:
Comment 1 Satit 2019-07-22 11:08:49 UTC
1. If I save as ods format, it display correctly when reopen.
2. I also test on version and still get the same bug.
Comment 2 Satit 2019-07-22 11:15:46 UTC
Created attachment 152938 [details]
Test file for bug of subtotal on XLSX
Comment 3 Satit 2019-07-22 11:19:13 UTC
I make a mistake in the "Steps to reproduce"
The AAAA items amount is 3000 instead of 4000. it should be as follow
1. ENTER ALL ROWS and subtotal as following:
ITEMS 6000 <-- subtotal(B3:B10)
----- ----
AAAA  3000
XXXX  1000
XXXX  2000

Comment 4 m_a_riosv 2019-07-22 20:42:42 UTC
Version: (x64)
Build ID: d4aa418fbfb9bd23e05fa739f20363bc299570d5
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
TinderBox: Win-x86_64@42, Branch:libreoffice-6-3, Time: 2019-07-10_11:36:58
Locale: es-ES (es_ES); UI-Language: en-US
Calc: CL

Version (Build ID: e183d5b)

Not repro
LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-
Comment 5 Roman Kuznetsov 2019-08-10 16:15:54 UTC
I can't repro it in 5.4 nor in 6.1 nor in 6.2 nor in 6.3 on Windows with file from attach

Satit, can you retest it in LibreOffice with clean user profile?
Comment 6 Satit 2019-08-12 05:21:17 UTC
I do a clean install with Libre v6.2.5.2. Initially, It work correctly.

But when I set Calc. Option> Formula (On Excel) > Always recalculate OR Prompt user, then reopen the xlsx, the subtotal will give wrong result as above.

I also test on v. with clean installation & set option as above. It also give the wrong result.
Comment 7 Satit 2019-08-12 05:33:12 UTC
The clean installation set Calc formulation option to "Never recalculate" which I do not want this option.

I've just found as above post that gives more detail steps to reproduce the bug.
Comment 8 b. 2019-12-17 16:44:49 UTC
repro with steps from comment #6 with ver: 

Version: (x64)
Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU threads: 8; OS: Windows 6.1; UI render: GL; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US

(subtotal needs the '9' to shrink to visible rows, was different in your ascii-graph)
Comment 9 Satit 2019-12-18 02:30:54 UTC
Ref. Comment 8 (subtotal needs the '9' to shrink to visible rows, was different in your ascii-graph)

I wrong typed subtotal function, actually it had 9 as arguments in all samples above. The behavior also occurs in v. and v.
Comment 10 m_a_riosv 2019-12-18 11:14:55 UTC
Seems to me than when the file is open, even the row 3 is hide, it's not marked as such.
Only opening the filter, and marking to show also the empty, then the subtotal is properly recalculated.
In the file row 3 is marked as hidden (worksheets/Sheet1.xml):
<row r="3" customFormat="false" ht="12.8" hidden="true" customHeight="false" outlineLevel="0" collapsed="false"><c r="A3" s="3" t="s"><v>2</v></c><c r="B3" s="4" t="n"><v>3000</v></c></row>
Comment 11 Eike Rathke 2019-12-18 14:25:03 UTC
Problem seems to be that in the Excel file format there is no differentiation between filtered rows and manually hidden rows, there is just a hidden="true" attribute which on import does not result in a filtered row.

Can someone with Excel please create a short sample with an AutoFilter range where one row is filtered (which should not be included in a SUBTOTAL(9;...)) and one row is manually hidden (which should be included in a SUBTOTAL(9;...)) and attach it here? Thanks.
Comment 12 Eike Rathke 2020-02-20 15:56:24 UTC

*** This bug has been marked as a duplicate of bug 99913 ***