Bug 115022 - subtotal in .xlxs wrong on fileopen with Recalculation on File Load
Summary: subtotal in .xlxs wrong on fileopen with Recalculation on File Load
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Whiteboard: target:7.2.0
Keywords: filter:xlsx
: 119913 (view as bug list)
Depends on:
Blocks: Function-Subtotal XLSX-Autofilter
  Show dependency treegraph
Reported: 2018-01-15 12:18 UTC by p_kongstad
Modified: 2021-05-07 19:19 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:
Regression By:

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

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 Comment hidden (obsolete)
Comment 3 m.a.riosv 2018-01-15 23:20:52 UTC Comment hidden (obsolete)
Comment 4 p_kongstad 2018-01-16 11:20:32 UTC Comment hidden (obsolete)
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: (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 and 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 Comment hidden (obsolete)
Comment 10 Roman Kuznetsov 2019-01-21 19:21:33 UTC
still repro in

Version: (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
Comment 11 QA Administrators 2021-01-21 04:05:38 UTC Comment hidden (obsolete)
Comment 12 p_kongstad 2021-01-23 21:12:38 UTC

This issue is still present in Version:
Build ID: 00(Build:1)
CPU threads: 4; OS: Linux 5.8; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.0.3-0ubuntu0.20.10.1
Calc: threaded
Comment 13 Timur 2021-02-23 09:31:53 UTC
(In reply to p_kongstad from comment #4)
> (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?

1. Only ODF is native to LO and saving to OOXML may produce an error, so real check is with saving,not just opening. 
2. QA has many bugs and no time ro recreate the sample in each bug, reporter should always do it. 

That being said, I reproduce in LO 7.2+.
Comment 14 Timur 2021-02-23 09:41:15 UTC
*** Bug 119913 has been marked as a duplicate of this bug. ***
Comment 15 NISZ LibreOffice Team 2021-05-07 09:27:29 UTC
I can no longer reproduce this in 7.2 master after:


author	Tünde Tóth <toth.tunde@nisz.hu>	Wed Mar 24 14:07:15 2021 +0100
committer	László Németh <nemeth@numbertext.org>	Tue Mar 30 13:36:24 2021 +0200

tdf#99913 XLSX import: set filtered flag for rows

Thanks Tünde for making this go away too :).
Comment 16 Commit Notification 2021-05-07 14:47:09 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":


tdf#115022: sc_subsequent_filters: Add unittest

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:

Affected users are encouraged to test the fix and report feedback.