Description: =COUNTIF(<absolute range in E column>;<E column in currentline>) gives different counts for the same criteria First column's formula is : =COUNTIF(E$3:E$1168;E266) where the criteria E266 is in the current line 4 leclerc carrelage VOLUME 238051 3011932003301 2 post-optimisation VOLUME 238051 3011932003301 2 post-optimisation VOLUME 238051 3011932003301 4 Produits restants VOLUME 238051 3011932003301 Steps to Reproduce: 1.fill a range column with repeated words 2.insert a column on the left 3.type =COUNTIF(<absolute range>;<criteria>) example : range=E$3:E$1168 criteria E3 on line 3 4. drag last cell to fill the whole formula column for the data range 5. on the data header line, do an autofilter 6. select a value in the formula column. Actual Results: The bugged behavior shows different COUNTIF values for the same criteria Expected Results: The normal behavior should always show the same COUNTIF values for the same criteria Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.9 CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: x11; Locale: fr-FR (fr_FR.UTF-8); Calc: group
6.0.7 is EOL. Could you give a try to a recent LO version (6.2.7 or brand new 6.3.1)?
Created attachment 154262 [details] a calc file that exhibits the buggy behavior
I tried the same on the 6.2.6.2 version of LibreOffice on Linux (build 1:6.2.6-0ubuntu0.19.04.1): the same behavior was observed. I tried something more: 1) I added a first column to compare two immediate values in the range column : =EXACT(E3;E4) : it says that all equals values are equal (correct behavior) 2) dragging the first column in the range column onto the whole range. Then the counif values columns are becoming equal and correct. The values in the range are pure numbers. However, are they considered differently because some would be assimilated to TEXT values and other to NUMBER values ? As you can see in the attached file, the count is only correct in the first and last lines.
2 cells are Text, 2 cells are numbers To check this, you can type in H3: =ESTTEXTE(E3) Then copy the formula for the others. Now why count.if takes into account Text + number if Text in criteria and number only if number in criteria, I don't know.
Hmm, we went to the same conclusion. Winfried: any thoughts about this case?
This is the process I used to put data: 1) copy paste from tab separated fields text to the spreadsheet using csv import feature. I selected all input columns and parametrized them as TEXT 2) I happened to insert/delete rows inside the data area in the spreadsheet. Maybe this set the format to the default on these lines. 3) thereafter I inserted the COUNTIF formula column and filled it
same result with AOO 4.1.5
Created attachment 154268 [details] countif_excel_vs_lo excel results to "4" in all cases, but warns about values formatted as text
If I'm not wrong only direct references are interpreted as number, in fact SUM() doesn't sum the text numbers but with =E3+E4+E5+E6 all are summed. IMHO not a bug.
Created attachment 154277 [details] simplified use cases (In reply to Julien Nabet from comment #5) I can explain what happens: -if the cell to be compared and the criterion are of the same type, the comparison result is true; -if the cell to be compared is of type numeric and the criterion is of type text, the comparison result is true, i.e. the cell to be compared is evaluated as text; -if the cell to be compared is of type text and the criterion is of type numeric, the comparison result is false. (see uploaded document, rows 3..6) COUNTIF is defined in http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#COUNTIF and the criterion is defined in http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#Criterion. In these paragraphs I cannot find explicit statements accepting or rejecting the behaviour as described above. The '=' operator, which can be used in the criterion, is defined in http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#Infix_Operator_EQ. Here the use of two different type should produce false as result. If we use '=' in the criterion Calc does not comply with this. (see uploaded document, rows 8..11) I cannot say which is fully compliant with the ODFF standard and which is not, probably Eike can tell. Excel produces a result true for all 8 combinations used in the uploaded document. However, if COUNTIF in Calc is compliant with the ODFF standard, COUNTIF is not at fault, but we may need an Excel-compliant version of COUNTIF.
(In reply to Winfried Donkers from comment #10) > Created attachment 154277 [details] > simplified use cases > > (In reply to Julien Nabet from comment #5) > > I can explain what happens: > -if the cell to be compared and the criterion are of the same type, the > comparison result is true; > -if the cell to be compared is of type numeric and the criterion is of type > text, the comparison result is true, i.e. the cell to be compared is > evaluated as text; > -if the cell to be compared is of type text and the criterion is of type > numeric, the comparison result is false. > (see uploaded document, rows 3..6) Except using "isNumeric"/"isText" function, how to distinguish them and above all how to change them? I only that know that if I put a simple quote before a number, it's considered as text but the simple quote stays. Also, when clicking to modify, sometimes the number is aligned at left, sometimes at right. > > COUNTIF is defined in > http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2. > html#COUNTIF and the criterion is defined in > ... > Excel produces a result true for all 8 combinations used in the uploaded > document. However, if COUNTIF in Calc is compliant with the ODFF standard, > COUNTIF is not at fault, but we may need an Excel-compliant version of > COUNTIF. Just my personal opinion but ODFF standard should be the top priority compared to Excel compatibility.
(In reply to Julien Nabet from comment #11) > > (see uploaded document, rows 3..6) > Except using "isNumeric"/"isText" function, how to distinguish them and > above all how to change them? > I only that know that if I put a simple quote before a number, it's > considered as text but the simple quote stays. > Also, when clicking to modify, sometimes the number is aligned at left, > sometimes at right. Simply use Ctrl-F8 or View-Value highlighting.
(In reply to Winfried Donkers from comment #10) > Created attachment 154277 [details] > simplified use cases > > (In reply to Julien Nabet from comment #5) > > I can explain what happens: > -if the cell to be compared and the criterion are of the same type, the > comparison result is true; > -if the cell to be compared is of type numeric and the criterion is of type > text, the comparison result is true, i.e. the cell to be compared is > evaluated as text; > -if the cell to be compared is of type text and the criterion is of type > numeric, the comparison result is false. > (see uploaded document, rows 3..6) > > COUNTIF is defined in > http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2. > html#COUNTIF and the criterion is defined in > http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2. > html#Criterion. In these paragraphs I cannot find explicit statements > accepting or rejecting the behaviour as described above. > The '=' operator, which can be used in the criterion, is defined in > http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2. > html#Infix_Operator_EQ. Here the use of two different type should produce > false as result. If we use '=' in the criterion Calc does not comply with > this. (see uploaded document, rows 8..11) > I cannot say which is fully compliant with the ODFF standard and which is > not, probably Eike can tell. > > Excel produces a result true for all 8 combinations used in the uploaded > document. However, if COUNTIF in Calc is compliant with the ODFF standard, > COUNTIF is not at fault, but we may need an Excel-compliant version of > COUNTIF. I agree. Let's turn this into an enhancement then. Thanks for the great analysis
a little OT here, but please be aware that the format applied to a cell isn't evaluated after a change (of the format), but needs a subsequent 'touch' to the content. thus there are two states of cells, one 'content matches formatting', and another 'the content is evaluated according to the previous formatting'. (and plenty more or 'subtypes' where content and format contradict each other ...) i mentioned that as a 'trap' earlier, but Mike Kaganski? insisted it has to be kept that way. imho., as it's difficult to see which is what, it introduces difficulties in use and debug ... reg. b.