Bug 105044 - SUMIF and SUMIFS give wrong results when empty cells
Summary: SUMIF and SUMIFS give wrong results when empty cells
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Bernhard
URL:
Whiteboard: target:5.4.0
Keywords:
Depends on:
Blocks:
 
Reported: 2017-01-02 10:44 UTC by michelgre
Modified: 2019-02-06 19:02 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example of the bug (9.39 KB, application/vnd.sun.xml.calc)
2017-01-02 10:48 UTC, michelgre
Details

Note You need to log in before you can comment on or make changes to this bug.
Description michelgre 2017-01-02 10:44:43 UTC
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
Comment 1 michelgre 2017-01-02 10:48:33 UTC
Created attachment 130088 [details]
Example of the bug

The incorrect results are in red.
Comment 2 Buovjaga 2017-01-05 08:43:26 UTC
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
Comment 3 Bernhard 2017-04-06 16:54:43 UTC
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
Comment 4 Commit Notification 2017-04-10 21:58:25 UTC
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.
Comment 5 Eike Rathke 2017-04-10 22:15:22 UTC
(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.
Comment 6 Bernhard 2017-04-11 08:01:50 UTC
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?