Description: When using COUNTIFS on exactly 2 range and counting the number of empty cells on the 1st range, if the 1st on contains at least one not empty cell then all empty cells after the last not empty cells are not taken in account. Steps to Reproduce: 1. Fill : 1st row : A1, x; B1, ; C1, x ; D1, ; 2nd row : A2, x; B2, x; C2, x ; D2, x; 2. In A3 : =COUNTIFS(A1:D1,"=",A2:D2,"=x") Actual Results: 1 Expected Results: 2 Reproducible: Always User Profile Reset: No Additional Info: Version: 7.6.5.2 (X86_64) / LibreOffice Community Build ID: 38d5f62f85355c192ef5f1dd47c5c0c0c6d6598b CPU threads: 12; OS: Windows 10.0 Build 22631; UI render: Skia/Vulkan; VCL: win Locale: en-GB (fr_FR); UI: fr-FR Calc: CL threaded
This has been a problem since LO 7.4. This problem is related to tdf#151958 and tdf#159544, among others. This tdf#159858 shows a simple case to test for the bug(s). For an example similar to that described in this tdf#159858 comment 0, using semicolon as argument separator: |A|B|C|D|E|F|G|H 1|x||x||x||x| 2|x|x|x|x|x|x|x|x where "||" means an empty cell, and; _ |A|B|C|D|E|F|G|H represent the respective column headers, and; _ the initial "1" and "2" in each line represent the respective row headers. 1. =COUNTIFS(A1:H1;"=";A2:H2;"x") fails since LO 7.4 up until recent 24.8 alpha. 2. =COUNTIFS(A1:H1;"=";A2:H2;"=x") fails in (only) part of the range of versions, including recent LO 24.8 alpha. 3. =COUNTIFS(A2:H2;"x";A1:H1;"=") overcomes the failure, perhaps just by chance. IOW, when the empty cells are evaluated last, the result seems correct (similar to tdf#159544). CC'ing Eike and Xisco.
Created attachment 192831 [details] Example file
(In reply to ady from comment #1) > This has been a problem since LO 7.4. > > This problem is related to tdf#151958 and tdf#159544, among others. This > tdf#159858 shows a simple case to test for the bug(s). > > For an example similar to that described in this tdf#159858 comment 0, using > semicolon as argument separator: > > |A|B|C|D|E|F|G|H > 1|x||x||x||x| > 2|x|x|x|x|x|x|x|x > > where "||" means an empty cell, and; > _ |A|B|C|D|E|F|G|H represent the respective column headers, and; > _ the initial "1" and "2" in each line represent the respective row headers. > > > 1. > =COUNTIFS(A1:H1;"=";A2:H2;"x") > > fails since LO 7.4 up until recent 24.8 alpha. > > > 2. > =COUNTIFS(A1:H1;"=";A2:H2;"=x") > > fails in (only) part of the range of versions, including recent LO 24.8 > alpha. With the example file, result for both after hard recalc (Ctrl+Shift+F9) is 3 while it should be 4 starting at commit 7674399aac661eb503d7badc53b9a4d68bd9839d. Tested with linux-64-7.4. So this is actually a dupe of bug 159544. *** This bug has been marked as a duplicate of bug 159544 ***
Created attachment 192832 [details] Example with results of several LO versions This file shows the most simple case of the problem, with the results of the same formulas calculated with diverse versions of LO Calc. Since this is the simplest case, I would suggest setting this report as the main one, and the others as dupes of this report.
(In reply to Buovjaga from comment #3) > With the example file, result for both after hard recalc (Ctrl+Shift+F9) is > 3 while it should be 4 starting at commit > 7674399aac661eb503d7badc53b9a4d68bd9839d. Tested with linux-64-7.4. There must be 2 reasons for the bug(s), instead of just 1 unique commit. Please see attachment 192832 [details] that proves it.
(In reply to ady from comment #4) > Created attachment 192832 [details] > Example with results of several LO versions > > This file shows the most simple case of the problem, with the results of the > same formulas calculated with diverse versions of LO Calc. > > Since this is the simplest case, I would suggest setting this report as the > main one, and the others as dupes of this report. I wonder what explains the different result compared to my example file?
(In reply to Buovjaga from comment #6) > I wonder what explains the different result compared to my example file? In attachment 192832 [details], please note the result for LO 7.4.3.2 for each of the formulas, and compare the results with the other versions. Such comparison shows that there are at least 2 separated issues – I am _guessing_ that it could be possibly related to the temporal patches in tdf#151958, but yet to be proven.
(In reply to Buovjaga from comment #6) > (In reply to ady from comment #4) > > Created attachment 192832 [details] > > Example with results of several LO versions > > > > This file shows the most simple case of the problem, with the results of the > > same formulas calculated with diverse versions of LO Calc. > > > > Since this is the simplest case, I would suggest setting this report as the > > main one, and the others as dupes of this report. > > I wonder what explains the different result compared to my example file? Aah, Heisenberg's revenge: try doing hard recalc *twice*!