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
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 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 ***