Description: Starting with V. 6.1 the function SUMIF creates wrong results. When using "<> 0" as criteria the function adds all content of Sum Range without checking the range content. Problem occurs since switching from V. 6.0.6.2 (x64)to V. 6.1.2.1 (x64) see example Example_Inequality.ods Steps to Reproduce: see Example_Inequality.ods Actual Results: see Example_Inequality.ods Expected Results: see Example_Inequality.ods Reproducible: Always User Profile Reset: No Additional Info: [Information automatically included from LibreOffice] Locale: en-US Module: SpreadsheetDocument [Information guessed from browser] OS: Windows (All) OS is 64bit: no
Created attachment 147004 [details] Example for SUMIF failure in combination with Inequality
And what happens if you put 0 to B5 and B6?
attached file has been saved with lo 6.1.3.2 checked with new userprofiles: - open attached file with lo 6.0.7.1 - cell C10 shows 63 - ctrl+shift+F9 (calculate all) - cell C10 shows 13 - open attached file with lo 6.1.3.2 - cell C10 shows 63 - ctrl+shift+F9 (calculate all) - now cell C10 shows 63 so something has changed - open attached file with lo 6.0.7.1 - ctrl+shift+F9 (calculate all) - save and close - open with lo 6.1.3.2 - cell C10 shows 13 - ctrl+shift+F9 (calculate all) - now cell C10 shows 63
Created attachment 147007 [details] example excel 2016 result with excel 2016 is 63
code changed with: Resolves: tdf#73081 *) empty <> not-empty is also a match if numeric This simplifies to match empty for the same conditions whether numeric or not, an empty string does not result in numeric 0 here. https://gerrit.libreoffice.org/plugins/gitiles/core/+/88f10aed35c19cc43a88bbcd12b026cb32c0003f *) tdf#73081 COUNTIF with criterium <>number does not count empty cells. https://bugs.documentfoundation.org/show_bug.cgi?id=73081
(In reply to Oliver Brinzing from comment #5) So, intended change => NOTABUG? Just wonder why a release note missing.
the changed calulation started with lo 6.1.3.2, lo 6.1.3.1 shows the previous behavior. as mentioned above: opening a spreadsheet created with lo 6.1.3.2 in an older version now will change formula result afer a hard recalc. the change was intended: an empty cell and a cell with formula ="" are same now (treated as <> 0), formula ="0" is 0. the "[ ] Treat empty string as zero" compatibility option seems to have no influence. is this correct? and the question is: which formula's are affected? at least the release notes should mention the change and the documentaion should be adjusted.
(In reply to Oliver Brinzing from comment #7) > the "[ ] Treat empty string as zero" compatibility option seems to have no > influence. is this correct? Citing bug 73081 comment 0: > Notice the part "including empty cells". > > Notice, that this is not about the problem, whether a zero-length string is > to be considered as empty cell. So clearly this is the case where empty cells and zero-length-strings cells can be treated differently. > and the question is: which formula's are affected? citing bug 73081 comment 14: > So COUNTIF() is inconsistent and now Calc does the same. Some proper formulas for current state: =SUMPRODUCT(A2:A7;B2:B7<>"";B2:B7<>0) =SUMIFS(A2:A7;B2:B7;"<>0";B2:B7;"<>")
(In reply to Mike Kaganski from comment #8) > =SUMPRODUCT(A2:A7;B2:B7<>"";B2:B7<>0) will not work with excel (result is 0) > =SUMIFS(A2:A7;B2:B7;"<>0";B2:B7;"<>") will work with excel too (result 13) can we assign this issue to eike and regina for review?
(In reply to Oliver Brinzing from comment #9) > can we assign this issue to eike and regina for review? I don't think we can "assign" the issue to anyone. But of course, we can ask, and actually I have CC'ed Eike already, in the hope that he will come with necessary clarifications, if he finds useful. And CCing Regina might be useful as well. I believe that the linked issue explains it rather extensively, though, so maybe the question is in the realm of Olivier?
So yes, the "<>0" condition matches empty cells now, to comply with ODFF and interoperability with other spreadsheet implementations, see also comment 3 about Excel that does the same. The previous behaviour that excluded empty cells was wrong and a bug. An empty cell is not a number 0 in this context. See also ODFF 4.11.8 Criterion http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__434502_49873571 "For <>, if the value is not empty it matches any cell content except the value, including empty cells. "
*** Bug 122321 has been marked as a duplicate of this bug. ***