Description: The result of SUMIF and SUMIFS is wrong when the test range contains an empty cell and the criterium is "different from a given string". Example: SUMIF(A1:A7;"<>X";B1:B7) => does not sum up cells having the A column epmty. While: SUMIF(A1:A7;"=";B1:B7) or SUMIF(A1:A7;"";B1:B7) gives a correct result. See attached sheet. Steps to Reproduce: 1.Prepare a column of 7 string having some empty cells and a column of 7 numbers 2.Put the formula : SUMIF(A1:A7;"<>X";B1:B7) 3.The result is not correct. Actual Results: Empty cells are not counted in the sum. Expected Results: Empty cells should be counted since they are different from "X" Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0
Created attachment 130088 [details] Example of the bug The incorrect results are in red.
Confirmed already in 3.3 on the part of SUMIF. Arch Linux 64-bit, KDE Plasma 5 Version: 5.4.0.0.alpha0+ Build ID: 1a58cdf8af1aba52ce0a376666dd7d742234d7cf CPU Threads: 8; OS Version: Linux 4.8; UI Render: default; VCL: kde4; Locale: fi-FI (fi_FI.UTF-8); Calc: group Built on January 4th 2016 Arch Linux 64-bit LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4
SUMIF and SUMIFS in Calc also treat empty cells different than MS Excel. In Excel, all compare operators execpt = and <> (that is, <, >, <=, >=) always return false when one or both sides are empty
Bernhard Widl committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=78dfedec7a7ba289691a5d18441796541f3b1812 tdf#105044 match empty cells on "<> not-empty" comparisons It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Bernhard from comment #3) > SUMIF and SUMIFS in Calc also treat empty cells different than MS Excel. > In Excel, all compare operators execpt = and <> (that is, <, >, <=, >=) > always return false when one or both sides are empty Where is that different from Calc? With =SUMIF(range,condition,sumrange) for any empty cell in range the condition never matches so the corresponding sumrange cell is not included.
The difference between Calc and Excel is if the condition operand is empty, so the condition is "less-than-empty" etc. In those cases, the condition string is only the compare operator ("=", "<>", "<", ">", "<=", ">=") In Excel, empty cells are only matched in "equal-to-empty" and "different-from-nonempty" conditions. The latter one has been fixed in this patch. Calc matches empty cells in "greater-than-empty" and "greater-or-equal-to-empty" conditions. It is debatable if "empty" is "less-than-nonempty", "less-or-equal-to-nonempty", "greater-or-equal-to-empty" or "less-or-equal-to-empty" and so should be matched (especially when comparing by string, not by value), but since Excel does not, why should Calc?