Created attachment 205087 [details] Query range with empty cells Open attached document. On tab "horizontal" you see the wrong result value 1. On tab "horizontal_2" you see the correct result value 4 for the same formulas. The reason is, that the query range is clamped the used columns in https://opengrok.libreoffice.org/xref/core/sc/source/core/data/queryiter.cxx 1747 maParam.nCol2 = rDoc.ClampToAllocatedColumns(nTab, maParam.nCol2); In case the criterion is "empty cell" the range must not be clamped. The problem exists only for horizontal count. The vertical count is always correct, as can be seen on tab "vertical". Tested with Version: 24.8.0.3 (X86_64) / LibreOffice Community Build ID: 0bdf1299c94fe897b119f97f3c613e9dca6be583 CPU threads: 32; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Vulkan; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: threaded and with Version: 26.8.0.0.alpha0+ (X86_64) Build ID: 680(Build:0) CPU threads: 32; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Vulkan; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: threaded This is not duplicate to bug 159544, because different code parts are the reason.
It turned out that the solution for XLOOKUP will fix COUNTIF as well. Thus I'll set this to duplicate. *** This bug has been marked as a duplicate of bug 170295 ***
FWIW... In attachment 205087 [details] please be aware that the actual (incorrect) result depends on the version of Calc. For instance, older versions would provide 0 (zero) as the result, whereas newer ones would return 1 (one), both results being incorrect. IOW, in order to fully test this case (e.g. to add a unit test of sorts), it is not enough to see that the initial number is incorrect, but rather an additional step is needed (otherwise, someone might think that this is an inherited bug). For instance: 1. Open attachment 205087 [details]. 2. Recalculate Hard (i.e. press Control-Shift-F9); note that cells A3 and A6 still show the incorrect result. 3. In cell B9 (the specific cell address is relevant for the test): 3.1 Type-in =ISBLANK(B2:D2) and then press Control-Shift-Enter (CSE) (please note the range argument). 3.2 Press Control-Shift-F9 (Recalculate Hard); note that cells A3 and A6 still show the incorrect result. 4. Select the cell's range B9:D9. 5. Press Control-C (copy). 6. On cell C10: 6.1 (the specific cell address is relevant for the test), Control-V (paste). 6.2 Control-Shift-F9; note that cells A3 and A6 still show the incorrect result, but (probably) different than before. 7. On cell D11, repeat steps 6.1 and 6.2. 8. On cell E12, repeat steps 6.1 and 6.2. After Recalculate Hard, this is the first time that the result is as expected. 9. On cell E13, repeat steps 6.1 and 6.2. After Recalculate Hard, the correct result should still be displayed. This procedure shows the _current_ (2026-01-20) issue, to differentiate it from older cases in which COUNTIF* would also show incorrect results.
The reason for the wrong results is, that the query range is reduced to the actual used columns. Columns that have only empty cells and no cell background and no cell border are not "used". The patch https://gerrit.libreoffice.org/c/core/+/197703 changes the implementation so, that this reduction is no longer applied in case empty cells are queried. BTW, as workaround you can apply a background color to the query range.