Bug 127624 - Add Excel-compliant version of COUNTIF
Summary: Add Excel-compliant version of COUNTIF
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2019-09-18 13:52 UTC by Alain Touret
Modified: 2020-05-03 17:45 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


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.
Description Alain Touret 2019-09-18 13:52:28 UTC
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
Comment 1 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)?
Comment 2 Alain Touret 2019-09-18 13:56:52 UTC
Created attachment 154262 [details]
a calc file that exhibits the buggy behavior
Comment 3 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.
Comment 4 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.
Comment 5 Julien Nabet 2019-09-18 14:17:18 UTC
Hmm, we went to the same conclusion.

Winfried: any thoughts about this case?
Comment 6 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
Comment 7 Oliver Brinzing 2019-09-18 17:52:36 UTC
same result with AOO 4.1.5
Comment 8 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
Comment 9 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.
Comment 10 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.
Comment 11 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.
Comment 12 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.
Comment 13 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
Comment 14 b. 2019-09-26 12:59:53 UTC Comment hidden (no-value)