Description: 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: 50 Expected Results: 5 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
Created attachment 131798 [details] Excel file
Created attachment 131799 [details] Calc file
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".
Looks like a dup of https://bugs.documentfoundation.org/show_bug.cgi?id=58874
Might be, or might be not. That one has also other functions involved. Let's just add it as related.
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.
This is fixed with the ongoing changes for bug 58874. *** This bug has been marked as a duplicate of bug 58874 ***