Bug 65059 - wrong evaluation of SUMPRODUCT when nested SUMIFS use ranges
Summary: wrong evaluation of SUMPRODUCT when nested SUMIFS use ranges
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.1.2-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 65058
Blocks:
  Show dependency tree
 
Reported: 2021-01-05 11:07 UTC by gerard.duong
Modified: 2021-04-09 08:27 UTC (History)
0 users


Attachments
test case for reproduction (1.77 KB, text/plain)
2021-01-05 11:07 UTC, gerard.duong 		Details
expected output if workbook written on file system (10.18 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-01-05 11:08 UTC, gerard.duong 		Details
simplified test case for bug reproduction (2.23 KB, text/plain)
2021-01-25 14:27 UTC, gerard.duong 		Details
Show Obsolete (1) 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 gerard.duong 2021-01-05 11:07:52 UTC 
Created attachment 37689 [details]
test case for reproduction

I'm managing an XFFS workflow within which I have a formula cell with a SUMIFS nside a SUMPRODUCT. But its evaluation give a bad result: it is different from Excel of Office 365.

The nested SUMIFS uses ranges as criteria. For example:
SUMPRODUCT(SUMIFS(B1:B3, C1:C3, D1:D3))

The evaluation is done via FormulaEvaluator.evaluateAll().



I'm providing a test case for more details.
Comment 1 gerard.duong 2021-01-05 11:08:49 UTC 
Created attachment 37690 [details]
expected output if workbook written on file system
Comment 2 Dominik Stadler 2021-01-23 13:13:46 UTC 
Is there a difference to this bug-report to bug #65058?
Comment 3 gerard.duong 2021-01-25 08:26:13 UTC 
Yes.

The bug #65058 is about an error occuring with some parameter management. It seems that this happens when a criteria range is starting at a different column *and* row from the initial formula.
This bug is more about the evaluation providing wrong results when we avoid the above parameters. Having done some more analysis, I have noticed that SUMIFS evaluations are not providing an array result when some criteria are multi-valued.
Comment 4 gerard.duong 2021-01-25 14:27:59 UTC 
Created attachment 37715 [details]
simplified test case for bug reproduction

New test case to focus on pure formula evaluation.