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: 65231
  Show dependency tree
 
Reported: 2021-01-05 11:07 UTC by gerard.duong
Modified: 2021-04-27 05:51 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

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.