Bug 120895

Summary:	SUBTOTAL function, doesn't work inside SUMPRODUCT while it works inside an SUM array
Product:	LibreOffice	Reporter:	m_a_riosv <miguelangelrv>
Component:	Calc	Assignee:	Eike Rathke <erack>
Status:	VERIFIED FIXED
Severity:	normal	CC:	erack, himajin100000, oliver.brinzing, rb.henschel, winfrieddonkers, xiscofauli
Priority:	medium
Version:	Inherited From OOo
Hardware:	All
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=58874
Whiteboard:	target:6.2.0
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	108827
Attachments:	Sample file for test purposes Screenshot of "Sample file for test purposes" opened in Excel365

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