Created attachment 37804 [details] test case for reproduction I'm managing an XSSF workbook within which I have a formula cell with a COUNTIF inside a SUMPRODUCT. But its evaluation gives a bad result: it is different from Excel of Office 365. The nested COUNTIF uses ranges as criteria. For example: SUMPRODUCT(COUNTIF(B1:B3, C1:C3)) The evaluation is done via FormulaEvaluator.evaluate(Cell). I'm providing a test case for more details. This bug is similar to 65059, but I'm not sure if it is really a duplicate: My work-in-progress fixes of the 65059 do not apply to this bug.
The Countif class did not change in the last release, so I believe this bug also applies to the 5.0.0-FINAL version.
Created attachment 37805 [details] expected output if workbook written on file system
Created attachment 38028 [details] Bug fixes
Thanks for the zip but the format is not what we normally accept. The usual formats are: A svn patch file or (my preference) a github pull request (https://github.com/apache/poi)
I tried to run the matchif tests you provided and they mostly fail. The code submission is really confusing and way out of date (not based on the latest trunk code).
Sorry for the blunt zip archive. This was intented to be "sorted" by myself (into one or two more bugs, and a real fix for the current bug), but I did not found yet some time to do it. I'll post again once it is done.
thanks - it would be nice to have smaller patch submissions - because they are easier to apply
Gerard - I put the values from original comment (with attachment 37804 [details]) on this issue into Numbers (a Mac equivalent of Excel) and the result of SUMPRODUCT(COUNTIF(B1:B3, C1:C3)) was 2 not 5