Bug 96595 - Incorrect result from sumproduct / subtotal / offset formula
Summary: Incorrect result from sumproduct / subtotal / offset formula
Status: RESOLVED DUPLICATE of bug 58874
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.4.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xls
Depends on:
Blocks:
 
Reported: 2015-12-19 14:10 UTC by Tony
Modified: 2016-01-26 14:05 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel test file (16.50 KB, application/vnd.ms-excel)
2015-12-19 18:06 UTC, Tony
Details
printscreen from LO and excel (72.25 KB, image/png)
2015-12-19 19:20 UTC, raal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tony 2015-12-19 14:10:25 UTC
The following formula should compute the number of visible rows in a filtered list that also match a further condition:-

=SUMPRODUCT((LEFT(BH2956:BH3990)="T")*SUBTOTAL(3,OFFSET($A2956:$A3990,ROW(C1:INDEX($C:$C,ROWS($C2956:$C3990)))-1,,1)))

In Excel this works fine, but when used in LO Calc it gives an incorrect result - in this case 420838 versus the correct value of 186.

Tested using Excel 2003 in Windows 7, versus LO Calc in Windows 7 and Arch Linux.
Comment 1 raal 2015-12-19 15:00:55 UTC
Please attach test file, thanks
Comment 2 m.a.riosv 2015-12-19 16:00:54 UTC
This report was requested by Eike on Ask.
https://ask.libreoffice.org/en/question/61729/whats-wrong-with-this-formula/?answer=61791#post-id-61791
Comment 3 Tony 2015-12-19 18:06:27 UTC
Created attachment 121410 [details]
Excel test file

test file showing incorrect result in LO Calc versus correct result in Excel 2003
Comment 4 raal 2015-12-19 19:20:15 UTC
Created attachment 121414 [details]
printscreen from LO and excel

I can confirm with Version: 5.2.0.0.alpha0+ (x64)
Build ID: cfe08df695c046371c4361a434176e6381e3e064
CPU Threads: 1; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-12-15_13:10:55
Comment 5 GerardF 2016-01-26 14:05:22 UTC

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