Bug 146811 - COUNTIF(...; "") does not count empty string formula results
Summary: COUNTIF(...; "") does not count empty string formula results
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2022-01-17 06:25 UTC by Mike Kaganski
Modified: 2022-12-02 13:27 UTC (History)
4 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 Mike Kaganski 2022-01-17 06:25:57 UTC
Make sure to *disable* Options->Calc->Calculate->Search criteria "=" and "<>" must apply to whole cells.

Put this formula in A1:

 =""

Put this formula in B1:

 =COUNTIF(A1:A10; "")

The observed result in B1 is 9, but is expected to be 10. Note that the result of '=COUNTIF(A1:A10; "=")' using comparator *is* expected to be 9, and it is OK in Calc. See COUNTIF documentation [1], and Criterion documentation [2] (the criterion "" matches the "Other Text value" clause there).

It shows the expected 10 when 'Search criteria "=" and "<>" must apply to whole cells' is enabled; this option corresponds to OpenFormula's 'HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL'; but it is unexpected that matching empty string against whole cell value would match, while it wouldn't for the same cell when not applying the criterion to whole cell.

A note: Excel 2016 gives 10; that would correspond to 'HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL' enabled, so not directly applicable as an argument. Rather, the problem here is that enabling HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL must give a strict *subset* of matches when HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is disabled.

Tested with Version: 7.3.0.1 (x64) / LibreOffice Community
Build ID: 840fe2f57ae5ad80d62bfa6e25550cb10ddabd1d
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL

and in Version: 6.0.0.3 (x64)
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765
CPU threads: 12; OS: Windows 10.0; UI render: GL; 
Locale: ru-RU (ru_RU); Calc: CL

[1] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#COUNTIF
[2] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#Criterion
Comment 1 LeroyG 2022-01-17 20:49:49 UTC
Both with =COUNTIF(A1:A10; "") and =COUNTIF(A1:A10; "=") I get 10 in B1.
Also I get 10 in B1 if I apply Formula to Value (menu Data - Calculate) in A1.

Version: 7.1.8.1 (x64) / LibreOffice Community
Build ID: e1f30c802c3269a1d052614453f260e49458c82c
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: default; VCL: win
Locale: es-MX (es_ES); UI: en-US
Calc: CL
Comment 2 Mike Kaganski 2022-01-18 06:50:51 UTC
(In reply to LeroyG from comment #1)

Thanks for testing; are you sure you have disabled Options->Calc->Calculate->Search criteria "=" and "<>" must apply to whole cells?
Comment 3 Mike Kaganski 2022-01-18 06:52:29 UTC
(In reply to Mike Kaganski from comment #2)

But yes, I have tested with 7.1.0.3, and there I also see 10 in both modes.
Comment 4 Buovjaga 2022-12-02 13:27:07 UTC
(In reply to Mike Kaganski from comment #0)
> Make sure to *disable* Options->Calc->Calculate->Search criteria "=" and
> "<>" must apply to whole cells.
> 
> Put this formula in A1:
> 
>  =""
> 
> Put this formula in B1:
> 
>  =COUNTIF(A1:A10; "")
> 
> The observed result in B1 is 9, but is expected to be 10. Note that the
> result of '=COUNTIF(A1:A10; "=")' using comparator *is* expected to be 9,
> and it is OK in Calc. See COUNTIF documentation [1], and Criterion
> documentation [2] (the criterion "" matches the "Other Text value" clause
> there).

Repro

Arch Linux 64-bit
Version: 7.5.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: 2b093d27b7e6f231512920f20089fcac291b021f
CPU threads: 8; OS: Linux 6.0; UI render: default; VCL: kf5 (cairo+xcb)
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Built on 2 December 2022