Bug 127624 - Add Excel-compliant version of COUNTIF
 Status: NEW None LibreOffice Unclassified Calc (show other bugs) Inherited From OOo All All medium enhancement Not Assigned Calc-Function Show dependency tree / graph

 Reported: 2019-09-18 13:52 UTC by Alain Touret 2020-05-03 17:45 UTC (History) 7 users (show) erack himajin100000 miguelangelrv oliver.brinzing serval2412 winfrieddonkers xiscofauli

Attachments
a calc file that exhibits the buggy behavior (10.60 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-18 13:56 UTC, Alain Touret
Details
countif_excel_vs_lo (74.19 KB, image/png)
2019-09-18 18:01 UTC, Oliver Brinzing
Details
simplified use cases (13.15 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-19 06:23 UTC, Winfried Donkers (retired)
Details

 Note You need to log in before you can comment on or make changes to this bug.
 Alain Touret 2019-09-18 13:52:28 UTC ```Description: =COUNTIF(;) 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(;) 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``` Julien Nabet 2019-09-18 13:55:28 UTC ```6.0.7 is EOL. Could you give a try to a recent LO version (6.2.7 or brand new 6.3.1)?``` Alain Touret 2019-09-18 13:56:52 UTC ```Created attachment 154262 [details] a calc file that exhibits the buggy behavior``` Alain Touret 2019-09-18 14:13:14 UTC ```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.``` Julien Nabet 2019-09-18 14:16:26 UTC ```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.``` Julien Nabet 2019-09-18 14:17:18 UTC ```Hmm, we went to the same conclusion. Winfried: any thoughts about this case?``` Alain Touret 2019-09-18 14:59:30 UTC ```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``` Oliver Brinzing 2019-09-18 17:52:36 UTC `same result with AOO 4.1.5` Oliver Brinzing 2019-09-18 18:01:16 UTC ```Created attachment 154268 [details] countif_excel_vs_lo excel results to "4" in all cases, but warns about values formatted as text``` m.a.riosv 2019-09-18 20:40:04 UTC ```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.``` Winfried Donkers (retired) 2019-09-19 06:23:29 UTC ```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.``` Julien Nabet 2019-09-19 07:13:36 UTC ```(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.``` Winfried Donkers (retired) 2019-09-19 07:59:49 UTC ```(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.``` Xisco Faulí 2019-09-25 15:53:52 UTC ```(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``` b. 2019-09-26 12:59:53 UTC Comment hidden (no-value) ```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.```