Created attachment 179943 [details] An XLSX with COUNTIFS and empty string cells The attachment contains these formulas in A1 and B1: =COUNTIFS(A2:A10;"=") =COUNTIFS(B2:B10;"<>") A2 and B2 are both: ="" A3:B10 are all empty. Excel gives 8 in A1, and 1 in B1. Calc gives (after recalc): 9 in A1, 0 in B1. Excel's behavior matches ODF v1.3, which tells: OASIS ODF 1.3 part 4 sect. 4.11.8 Criterion [1] > For <>, if the value is empty it matches non-empty cells. OASIS ODF 1.3 part 4 sect. 4.7 Empty Cell [2] > An empty cell is neither zero nor the empty string, and an empty cell can be > distinguished from cells containing values (including zero and the empty > string). An empty cell is not the same as an Error, in particular, it is > distinguishable from the Error #N/A (not available). So this is both standard-incompliance and interoperability bug in Calc. Tested with Version: 7.3.3.2 (x64) / LibreOffice Community Build ID: d1d0ea68f081ee2800a922cac8f79445e4603348 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 [1] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#Criterion [2] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#EmptyCell
On pc Debian x86-64 with master sources updated today and new profile, I could reproduce this. This part should interest you: https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/queryparam.cxx?r=14001bc7#263 At line 263 rItem.meType = ScQueryEntry::ByValue then it's changed line 264 to ScQueryEntry::ByString If I remove this change and so if we keep ScQueryEntry::ByValue, the recalc is ok. A comment block below tells: " 266 /* TODO: pFormatter currently is also used as a flag whether matching 267 * empty cells with an empty string is triggered from the interpreter. 268 * This could be handled independently if all queries should support 269 * it, needs to be evaluated if that actually is desired. */ "
Thanks Julien! Related: Commit 01b615687fe0f39c65e0e8290db434db2f1ef8ac Author Eike Rathke <erack@redhat.com> Date Tue Mar 31 14:01:42 2015 +0200 Resolves: tdf#35636 implement match on empty cells
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/920a083f3818765528899ab38170db5a2917a06c Resolves: tdf#148948 Use QueryBy(Non)Empty for "=" and "<>" criteria It will be available in 7.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/134054 for 7-3
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-3": https://git.libreoffice.org/core/commit/f52f599518b5b3aef78b7af1d3a62996eec53a1f Resolves: tdf#148948 Use QueryBy(Non)Empty for "=" and "<>" criteria It will be available in 7.3.4. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8bd91a40eae95f38c15d570d6af87b997c7eee39 tdf#148948: sc_statistical_functions: Add unittest It will be available in 7.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.