You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2021/01/05 10:22:52 UTC

[Bug 65058] New: SUMPRODUCT evaluation error with some SUMIFS cases

https://bz.apache.org/bugzilla/show_bug.cgi?id=65058

            Bug ID: 65058
           Summary: SUMPRODUCT evaluation error with some SUMIFS cases
           Product: POI
           Version: 4.1.2-FINAL
          Hardware: PC
            Status: NEW
          Severity: critical
          Priority: P2
         Component: XSSF
          Assignee: dev@poi.apache.org
          Reporter: gerard.duong@externe.bnpparibas.com
  Target Milestone: ---

Created attachment 37687
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=37687&action=edit
test case for bug reproduction

I'm managing an XFFS workflow within which I have a formula cell with a SUMIFS
nside a SUMPRODUCT. But its evaluation fails, whereas Excel 2016 just handles
it well.

Here is an example of failing formula: SUMPRODUCT(SUMIFS(B1:B3, C1:C3, F6:F7)).
This one would also fail: SUMPRODUCT(SUMIFS(B1:B3, C1:C3, D2:D3)).
... whereas this one works: SUMPRODUCT(SUMIFS(B1:B3, C1:C3, D1:D2)).



The generated exception:
java.lang.RuntimeException: Invalid arg type for SUMPRODUCT:
(org.apache.poi.ss.formula.eval.ErrorEval)

        at
org.apache.poi.ss.formula.functions.Sumproduct.evaluate(Sumproduct.java:83)
        at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:153)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:541)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:216)
        at
org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:56)
        at
org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:110)
        at
com.bnpparibas.sit.fin.asteria.formula.SumproductSumifsBugTest.testFormulaEvaluateAll(SumproductSumifsBugTest.java:68)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at
org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:628)
        at
org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:117)
        at
org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:184)
        at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at
org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:180)
        at
org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:127)
        at
org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
        at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
        at
org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
        at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
        at java.util.ArrayList.forEach(ArrayList.java:1257)
        at
org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
        at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
        at
org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
        at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
        at java.util.ArrayList.forEach(ArrayList.java:1257)
        at
org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
        at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
        at
org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
        at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
        at
org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
        at
org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
        at
org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
        at
org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
        at
org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
        at
org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
        at
org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
        at
org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
        at
org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
        at
com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:74)
        at
com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
        at
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
        at
com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 65058] SUMPRODUCT evaluation error with some SUMIFS cases

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65058

Dominik Stadler <do...@gmx.at> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 65058] SUMPRODUCT evaluation error with some SUMIFS cases

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65058

Dominik Stadler <do...@gmx.at> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|critical                    |major

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 65058] SUMPRODUCT evaluation error with some SUMIFS cases

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65058

gerard.duong@externe.bnpparibas.com changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #37687|0                           |1
        is obsolete|                            |
  Attachment #37688|0                           |1
        is obsolete|                            |

--- Comment #2 from gerard.duong@externe.bnpparibas.com ---
Created attachment 37713
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=37713&action=edit
simplified test case for bug reproduction

New test case to focus on pure formula evaluation.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 65058] SUMPRODUCT evaluation error with some SUMIFS cases

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65058

gerard.duong@externe.bnpparibas.com changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |INFORMATIONPROVIDED
             Status|NEW                         |RESOLVED
          Component|XSSF                        |SS Common

--- Comment #4 from gerard.duong@externe.bnpparibas.com ---
I'm on my way of fixing the 65059 bug. Just fixing it resolves this bug
actually.
So I've decided to close it.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 65058] SUMPRODUCT evaluation error with some SUMIFS cases

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65058

Dominik Stadler <do...@gmx.at> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Blocks|                            |65059


Referenced Bugs:

https://bz.apache.org/bugzilla/show_bug.cgi?id=65059
[Bug 65059] wrong evaluation of SUMPRODUCT when nested SUMIFS use ranges
-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 65058] SUMPRODUCT evaluation error with some SUMIFS cases

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65058

--- Comment #1 from gerard.duong@externe.bnpparibas.com ---
Created attachment 37688
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=37688&action=edit
expected output if evaluation worked

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 65058] SUMPRODUCT evaluation error with some SUMIFS cases

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65058

--- Comment #3 from gerard.duong@externe.bnpparibas.com ---
Created attachment 37714
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=37714&action=edit
Excel version of the test case

Excel version of the test case

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org