Bug 73081 - COUNTIF with criterium <>number does not count empty cells.
Summary: COUNTIF with criterium <>number does not count empty cells.
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.2.0 target:6.1.3
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2013-12-27 18:46 UTC by Regina Henschel
Modified: 2018-10-23 09:48 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Regina Henschel 2013-12-27 18:46:21 UTC
Make sure the cell B2 is empty, that is, it has no content at all
Write the number 7 into cell B3
Write the number 8 into cell B4
Write the formula =COUNTIF(B2:B4;"<>7") into cell A1
Expected result: 2
Seen result: 1

The specification says
"For <>, if the value is not empty it matches any cell content except the value, including empty cells."
[ODF1.2 part 2 section 4.11.8 Criterion]
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.
Comment 1 A (Andy) 2013-12-27 19:38:30 UTC
reproducible with LO 4.1.4.2 (Win7 Home, 64bit)
Comment 2 QA Administrators 2015-04-19 03:23:00 UTC Comment hidden (obsolete)
Comment 3 Regina Henschel 2015-04-19 21:50:57 UTC
The bug still exists in Version: 4.5.0.0.alpha0+
Build ID: 40b1e8266e47792d354cd457c652bfb0f0a21e69
TinderBox: Win-x86@62-TDF, Branch:MASTER, Time: 2015-02-11_00:13:43
Locale: de_DE
Comment 4 QA Administrators 2017-09-01 11:20:18 UTC Comment hidden (obsolete)
Comment 5 Regina Henschel 2017-09-01 15:27:01 UTC
The bug still exists in Version: 6.0.0.0.alpha0+
Build ID: 4c99b8a9de59f3c5280ff2944d9f828822897f4a
CPU threads: 4; OS: Windows 6.1; UI render: default; 
Locale: de-DE (de_DE); Calc: group
Comment 6 QA Administrators 2018-09-02 02:50:08 UTC Comment hidden (obsolete)
Comment 7 Regina Henschel 2018-09-02 10:46:39 UTC
The error still exists in Version: 6.2.0.0.alpha0+ (x64)
Build ID: 414ef6cb187dd3bbcc917dbedf3c0c1cc8668f60
CPU threads: 8; OS: Windows 10.0; UI render: GL; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2018-08-21_00:13:04
Locale: de-DE (en_US); Calc: CL
Comment 9 Oliver Brinzing 2018-09-24 10:18:24 UTC
result in aoo 4.1.5 is 1 too.

excel 2016 result is: 2
Comment 10 Commit Notification 2018-10-07 15:47:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9d8c89d9044edb5ad5c8bc4a8f0fd5868192714e

Resolves: tdf#73081 empty <> not-empty is also a match if numeric

It will be available in 6.2.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 11 Eike Rathke 2018-10-07 15:47:43 UTC
Pending review https://gerrit.libreoffice.org/61495 for 6-1
Comment 12 Eike Rathke 2018-10-07 15:58:03 UTC
Could someone please check what the Excel result in the test case scenario above for =AVERAGEIF(B2:B4,"<>7") is? 4 (counting the empty cell as in COUNTIF()) or 8 (not counting the empty cell)?
Comment 13 himajin100000 2018-10-07 16:00:54 UTC
(In reply to Eike Rathke from comment #12)
> Could someone please check what the Excel result in the test case scenario
> above for =AVERAGEIF(B2:B4,"<>7") is? 4 (counting the empty cell as in
> COUNTIF()) or 8 (not counting the empty cell)?

in Excel 2016, it was 8.
Comment 14 Eike Rathke 2018-10-08 09:21:21 UTC
Thanks. So COUNTIF() is inconsistent and now Calc does the same.
Comment 15 Commit Notification 2018-10-11 08:01:51 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=88f10aed35c19cc43a88bbcd12b026cb32c0003f&h=libreoffice-6-1

Resolves: tdf#73081 empty <> not-empty is also a match if numeric

It will be available in 6.1.4.

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 16 BogdanB 2018-10-12 16:24:44 UTC
Verified on

Version: 6.2.0.0.alpha0+
Build ID: 144da6d5079bcd435e6637cb5cf95305f3ec1306
CPU threads: 4; OS: Linux 4.15; UI render: GL; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-10-12_02:13:01
Locale: ro-RO (ro_RO.UTF-8); Calc: threaded
Comment 17 Commit Notification 2018-10-18 20:12:24 UTC
Zdeněk Crhonek committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b10a205cf162fd7b610c6efd5b444b1cbd920910

add test for bug tdf#73081

It will be available in 6.2.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 18 Commit Notification 2018-10-23 09:47:23 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ebf2c7c30423b5bba0a35e64e2e289820f2743a7&h=libreoffice-6-1-3

Resolves: tdf#73081 empty <> not-empty is also a match if numeric

It will be available in 6.1.3.

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.