Bug 106478 - INDIRECT function in array context compatibility with Excel
Summary: INDIRECT function in array context compatibility with Excel
Status: CLOSED DUPLICATE of bug 58874
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2017-03-10 14:43 UTC by weikelmann.frank
Modified: 2017-05-23 09:57 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Excel file (8.72 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-10 14:45 UTC, weikelmann.frank
Calc file (10.07 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-10 14:46 UTC, weikelmann.frank

Note You need to log in before you can comment on or make changes to this bug.
Description weikelmann.frank 2017-03-10 14:43:59 UTC
Calculate amount of "x" in a column.
“x” is a result of a function “WENN”
Use “=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(3:12)))*(B3:B12="x"))“ doing the calculation.
Calc get the result „50“; Excel 2016 get the result “5” (correct) using same input values and functions.
See attached files for reference.

Steps to Reproduce:
Use attached file.

Actual Results:  

Expected Results:

Reproducible: Always

User Profile Reset: No

Additional Info:

User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36
Comment 1 weikelmann.frank 2017-03-10 14:45:07 UTC
Created attachment 131798 [details]
Excel file
Comment 2 weikelmann.frank 2017-03-10 14:46:02 UTC
Created attachment 131799 [details]
Calc file
Comment 3 Eike Rathke 2017-03-10 15:49:05 UTC
Looks like Excel treats SUBTOTAL in array context different and it evaluates only a sequence of single elements, which may be related to that it can't operate on arrays at all. For example, this works in Calc =SUBTOTAL(3,{"x",""}) which results in 2, but Excel2010 doesn't even let you enter it insisting on input error.

Hence {=SUBTOTAL(3,INDIRECT("B"&ROW(3:12)))} in Excel results in an array of 1 values (here all 1 as all cells B3:B12 have content), whereas in Calc there's one result of 10 which due to vector replication is repeated for all array elements and SUMPRODUCT adds all values where there's a corresponding "x".
Comment 4 m.a.riosv 2017-03-11 00:54:51 UTC
Looks like a dup of https://bugs.documentfoundation.org/show_bug.cgi?id=58874
Comment 5 Eike Rathke 2017-03-13 12:21:53 UTC
Might be, or might be not. That one has also other functions involved. Let's just add it as related.
Comment 6 Eike Rathke 2017-03-14 22:29:19 UTC
See bug 58874, comment 11
Like with OFFSET this may be similar for INDIRECT constructed with an array (here ROW(3:12)) in that Excel internally processes a list of single arrays for which SUBTOTAL in array context acts on the individual arrays.
Comment 7 Eike Rathke 2017-05-23 09:56:41 UTC
This is fixed with the ongoing changes for bug 58874.

*** This bug has been marked as a duplicate of bug 58874 ***