Bug 120895 - SUBTOTAL function, doesn't work inside SUMPRODUCT while it works inside an SUM array
Summary: SUBTOTAL function, doesn't work inside SUMPRODUCT while it works inside an SU...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.2.0
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency tree / graph
 
Reported: 2018-10-25 09:34 UTC by m_a_riosv
Modified: 2022-11-02 12:10 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

Attachments
Sample file for test purposes (18.15 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-10-25 09:34 UTC, m_a_riosv 		Details
Screenshot of "Sample file for test purposes" opened in Excel365 (101.12 KB, image/png)
2018-10-30 19:42 UTC, Regina Henschel 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description m_a_riosv 2018-10-25 09:34:20 UTC 
Description:
SUBTOTAL function, doesn't work inside SUMPRODUCT while it works inside an SUM array

Steps to Reproduce:
1.Open attached file 
2.Different result with the same operation inside SUM as array and SUMPRODUCT
3.

Actual Results:
Different

Expected Results:
Both the same like SUM array


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
Before LibreOffice 6.0 none of both formulas work fine.
After 6.0 only SUM as array works fine.
Comment 1 m_a_riosv 2018-10-25 09:34:53 UTC 
Created attachment 145993 [details]
Sample file for test purposes
Comment 2 Xisco Faulí 2018-10-27 14:20:27 UTC 
@Eike, one for you ?
Comment 3 Eike Rathke 2018-10-30 18:18:29 UTC 
Investigating.

In the meantime, could someone please check if and how Excel supports those constellations? The peculiarity seems to be related to that the OFFSET() expression in array context here creates a list of references.
Comment 4 Regina Henschel 2018-10-30 19:42:37 UTC 
Created attachment 146179 [details]
Screenshot of "Sample file for test purposes" opened in Excel365
Comment 5 Eike Rathke 2018-11-01 17:53:12 UTC 
That's even worse than what LibreOffice does..
Comment 6 Commit Notification 2018-11-09 18:15:39 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/6ce84da750abcec3139bf12bc212fb2f03460add%5E%21

Resolves: tdf#120895 new ParamClass::ReferenceOrRefArray, tdf#58874 related

It will be available in 6.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:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2018-11-10 16:37:27 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/dc134874b62422729a6cbe62e48639e721eacfdf%5E%21

Unit test for array of references in ForceArray, tdf#120895, tdf#58874

It will be available in 6.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:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 m_a_riosv 2018-11-11 00:20:02 UTC 
Thanks Eike
Tested
Version: 6.2.0.0.alpha1+ (x64)
Build ID: bf4fc97131147d25b18d088023262c977f0b2787
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2018-11-10_01:58:33
Locale: es-ES (es_ES); UI-Language: en-US
Calc: threaded