Issue 123925

Summary:	COUNTIF with criterium <>number does not count empty cells
Product:	Calc	Reporter:	Regina Henschel <rb.henschel>
Component:	code	Assignee:	AOO issues mailing list <issues>
Status:	CONFIRMED ---	QA Contact:
Severity:	Normal
Priority:	P3	CC:	issues, oliver.brinzing, rainerbielefeld_ooo_qa
Version:	3.4.0
Target Milestone:	---
Hardware:	PC
OS:	Windows, all
See Also:	https://issues.apache.org/ooo/show_bug.cgi?id=123063 https://bugs.freedesktop.org/show_bug.cgi?id=73081
Issue Type:	DEFECT	Latest Confirmation in:	4.1.0-dev
Developer Difficulty:	---

Description Regina Henschel 2013-12-27 19:04:23 UTC

Make sure the cell B2 is empty, that is, it has no content at all
Write the number 7 into cell B3
Write the number 8 into cell B4
Write the formula =COUNTIF(B2:B4;"<>7") into cell A1
Expected result: 2
Seen result: 1

The specification says
"For <>, if the value is not empty it matches any cell content except the value, including empty cells."
[ODF1.2 part 2 section 4.11.8 Criterion]
Notice the part "including empty cells".

Notice, that this is not about the problem, whether a zero-length string is to be considered as empty cell.

Comment 1 Rainer Bielefeld 2013-12-28 08:31:39 UTC

Additional info:
(a) I agree, definitively not in accordance with ODF definition
(b) Competitors Behavior
(b1) Gnumeric 1.10.16:       As AOO result = 1
     Gnumeric 1.12.9:        As AOO result = 1
(b2) OOo 1.1.5. 2.0.2, 3.3.0 As AOO result = 1
(b3) Calligra 4.7.4:         As AOO result = 1
(b4) LibO 4.1.3:             As AOO result = 1
(b5) SoftMaker FreeOffice: Different to AOO result = 2
(b6) MS Excel 2010:        Different to AOO result = 2

(c) Although I see some applications for current behavior (most times nobody
    is interested in including empty cells), we should follow the ODF 
    definition. 
    Current behavior in Regina's example with correct behavior can be reached
    with  =COUNTIF(B2:B4;"<>7")-COUNTBLANK(B2:B4)

(d)  Already reproducible with  Pre-3.4.0 (OOo 1.1.5), but because of crippled  
     Version selector (Bug 123063) no useful info can be contributed

(e) Related to "Bug 98891 - COUNTIF cannot count blank cells"? May be this one
    is more or less a DUP? 

(f) Related to "Bug 65221 - SUMIF, COUNTIF, COUNTBLANK, DCOUNT, DSUM, ... 
    with empty cells and "" and ="" empty string criteria"?

(g) Related to "Bug 108991 - COUNTIF gives inconsistent counts of empty
    arrays"?

@Regina:
What do you think concerning relations?

Comment 2 Oliver Brinzing 2013-12-28 13:05:19 UTC

Comment 3 Regina Henschel 2013-12-28 17:04:26 UTC

@Rainer:
(a)(b) The specification is unambiguous here. For me it is not the question whether to change the value but when. Changing the result for an existing function is always difficult and a plan for change-over is needed.

(c)(d) COUNTBLANK has implementation dependent handling of zero-length strings and should be avoided in environments where interoperability is needed.
The concept of a namespace for formulas (especially the namespace "OpenFormula") and the exact definitions of function results for OpenFormula is new in ODF1.2. Therefore older behavior cannot determine the current results. But we need to think about a way to handle old documents. That problem exists in general, not only with function COUNTIF.
(e) Bug 98891 was a problem how to write a criterion. It is not relevant here.
(f) Bug 65221 is about handling of zero-length strings. Those are not included here. Therefore it is no duplicate. But bug 65221 needs to be re-examined carefully, because in the meantime OpenFormula is specified in ODF1.2.
(g) Bug 108991 is about handling of a constant matrix with missing element as part of the search range of COUNTIF. Constant matrices are new in ODF1.2. That is a very special case because of the array formula and should not be mixed with other problems. It is not related to this issue.

Comment 4 Rainer Bielefeld 2013-12-29 07:10:34 UTC

I remove irrelevant Bugw from "See Also"