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
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
(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?
(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.
(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