Bug 56655

Summary: XSSFFormulaEvaluator wrong result for SUMIFS and #VALUE!
Product: POI Reporter: Adrian Panasiuk <adek336+asf>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.10-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Attachments: test case

Description Adrian Panasiuk 2014-06-21 20:04:11 UTC
Created attachment 31742 [details]
test case

The following fails on 3.10-FINAL. Attached test case.

A1 := #VALUE!
B1 := SUMIFS(A:A,A:A,#VALUE!)

expected

A1 -> #VALUE!
B1 -> #VALUE!

actual

A1 -> #VALUE!
B1 -> 0
Comment 1 Dominik Stadler 2014-07-02 19:47:10 UTC
Did some analysis: Sumifs implementation validates the input-ranges, but not the PredicateMatcher. In this case the PredicateMatcher is ErrorMatcher and thus does not match anything, but also does not lead to InvalidValue being reported. 

We should first verify how Excel handles this, i.e. does it report #VALUE! when the matcher is reporting #VALUE!
Comment 2 Dominik Stadler 2015-01-07 14:34:06 UTC
Excel reports these as "#VALUE!".

There is now a reproducing unit test in class TestUnfixedXSSFBugs
Comment 3 Dominik Stadler 2015-06-20 13:11:21 UTC
I have fixed this now via r1686610, the sumifs implementation now verifies the criteria and returns the error value that it resolves to.