Description: 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. Ex. ALL 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: 6000 Expected Results: 3000 Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info:
1. If I save as ods format, it display correctly when reopen. 2. I also test on version 6.2.5.2 and still get the same bug.
Created attachment 152938 [details] Test file for bug of subtotal on XLSX
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 . . .
Repro Version: 6.3.0.1.0+ (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 3.6.7.2 (Build ID: e183d5b) Not repro LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4
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?
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.5.4.7.2 with clean installation & set option as above. It also give the wrong result.
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.
repro with steps from comment #6 with ver: Version: 6.2.8.2 (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 Calc: (subtotal needs the '9' to shrink to visible rows, was different in your ascii-graph)
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.6.3.3.2(x64) and v.6.3.4.2(x64)
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>
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.
*** This bug has been marked as a duplicate of bug 99913 ***