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
Dear Mike Kaganski, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Still repro using Version: 24.8.3.2 (X86_64) / LibreOffice Community Build ID: 48a6bac9e7e268aeb4c3483fcf825c94556d9f92 CPU threads: 24; OS: Windows 11 X86_64 (10.0 build 26100); UI render: default; VCL: win Locale: ru-RU (ru_RU); UI: en-US Calc: CL threaded. Given the comments 1, 3 - it would be good to bisect where it has changed between versions 7.1 and 7.3 (it likely changed between 6.0 and 7.1, and then again between 7.1 and 7.3).
The result was 0 before a change between last36onmaster and last40onmaster in the Linux 43all repo, within the range: https://git.libreoffice.org/core/+log/4b4ca8030285bd66526ff5bb2b6ea5a75a6c6bc7..d59024b652ccfaf7247da113ec36788fe260de74 The result changed to 10 in 6.1 with a953fa1c0f6a40a08859570516c511f3a8410a35 vlookup - optimize SC_EQUAL and NOT_EQUAL. The result changed back to 9 in 7.3 with 8dec2a98ce29251936cd45ebf864a89ff767ee50 ...which is not very surprising considering the commit subject: revert "vlookup - optimize SC_EQUAL and NOT_EQUAL." (tdf#139612)
Hello, I tried bisecting on the following: 7.1 and 7.2 > didn’t observe the issue. Observed the issue in 7.3: 7abde6a2c757905892159b505b43344acf63fc7e is the first bad commit commit 7abde6a2c757905892159b505b43344acf63fc7e (HEAD) Author: libreoffice <libreoffice@libreoffices-Mac-mini.local> Date: Sun Dec 5 10:01:32 2021 +0100 I made sure to uncheck the “Search criteria = and <> must apply to whole cells” during the bisecting. (Preference > LibreOffce Calc > Calculate > uncheck “Search criteria = and <> must apply to whole cells” > Select “Apply” > “OK”) Environment: • LibreOffice version: 7.4 • OS: macOS • Git version: 2.47.0 Please let me know if you need more information. Sincerely, Chika