Download it now!
Bug 115022 - subtotal in .xlxs
Summary: subtotal in .xlxs
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.4.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX Function-Subtotal
  Show dependency treegraph
 
Reported: 2018-01-15 12:18 UTC by p_kongstad
Modified: 2019-01-21 19:22 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
XLXS file with autofilter (5.57 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-15 12:18 UTC, p_kongstad
Details

Note You need to log in before you can comment on or make changes to this bug.
Description p_kongstad 2018-01-15 12:18:53 UTC
Created attachment 139108 [details]
XLXS file with autofilter

When opening a file with autofilter and subtotal saved as .xlxs the sum is totalling all values and not only the filtered. When saved as .ods this sum is showing the sum of the filtered values.

Expected that a file saved as .xlxs would sum the value correctly.

Also a hard recalculation does not update, but only editing the formula.


How to verify this bug

1. In row 1 we make in column A Index and B Amount
2. In column a we make to indexes called a and be in row 3 and row 7 the below values:
Index	amount
a	1
b	4
a	2
b	5
a	3
b	6

3. Now mark row 1 and use Data/Autofilter to enable filtering.
4. Open the filter using the dropdown for Column A and press on all to have no values marked.
5. Now mark value a
6. Use the icon for summing in B9.
7. This creates the formula =Subtotal(9,B2:B8) and returns the value 6 which is correct.
8. Now save the file as an ,XLXS file.
9. CLose the file and reopen it. Now the value is 21 which is incorrect.
Comment 1 p_kongstad 2018-01-15 12:26:00 UTC
Works properly in .xls format.
Comment 2 Xisco Faulí 2018-01-15 13:03:09 UTC
Could you please attach the document in .ODT format?
Comment 3 m.a.riosv 2018-01-15 23:20:52 UTC
I can't reproduce:
Version: 5.4.4.2 (x64)
Build ID: 2524958677847fb3bb44820e40380acbe820f960
CPU threads: 4; OS: Windows 6.19; UI render: default; 
Locale: es-ES (es_ES); Calc: group
Comment 4 p_kongstad 2018-01-16 11:20:32 UTC
(In reply to Xisco Faulí from comment #2)
> Could you please attach the document in .ODT format?

Why you want the attachment if .ODT when the issue is with .XLXS?
Comment 5 p_kongstad 2018-01-16 17:19:04 UTC
(In reply to m.a.riosv from comment #3)
> I can't reproduce:
> Version: 5.4.4.2 (x64)
> Build ID: 2524958677847fb3bb44820e40380acbe820f960
> CPU threads: 4; OS: Windows 6.19; UI render: default; 
> Locale: es-ES (es_ES); Calc: group

Try to change in Options/LibreOffice Calc/Formula/Recalculation on File Load/ Excel 2007 and newer: Always recalculate.
Comment 6 m.a.riosv 2018-01-16 21:42:12 UTC
Only happens with that option. And hard recalc doesn't work.
Comment 7 p_kongstad 2018-01-17 07:05:43 UTC
Also tested on 6.0.0.2 and 5.4.4.2 64 bit version on Windows with the same bug.
Comment 8 QA Administrators 2019-01-18 03:58:41 UTC Comment hidden (obsolete)
Comment 9 p_kongstad 2019-01-21 06:42:22 UTC
Hi,

The bug is still present in
Version: 6.1.4.2
Build ID: 1:6.1.4-0ubuntu0.18.04.1~lo1
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); Calc: group threaded
Comment 10 Roman Kuznetsov 2019-01-21 19:21:33 UTC
still repro in

Version: 6.3.0.0.alpha0+ (x64)
Build ID: eca59b6b8a0cf826ac59f77aec9acf045340c23f
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2019-01-16_03:48:12
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: threaded