Function COUNTIF with Empty Cells

English
, , ,
#1

Hello,

I’ve got a range A2:A11 of empty cells and a condition <>0. Evaluating the condition =IF(REF<>0) I do get the expected result FALSE for each cell (assume these results are put into range B2:B11) Now performing the follwing calulations:

  • =SUM(B2:B11) results in 0 (expected result)
  • {=COUNT(IF(A2:A11<>0))} results in 0 (expected result)
  • =SUMPRODUCT(COUNT(IF(A2:A11<>0))) results in 0 (expected result)

But

  • =COUNTIF(A2:A11;"<>0") results in 10 (unexpected result)

See the following screenshot and example file:

image description

Enviroment

  Version: 6.3.2.2, Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
  CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5;
  Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded

Settings Tools -> Options -> LibreOffice Calc -> Formula -> Cat: Detailed Calculation Settings -> Option: Default settings (though other custom settings tried did not influence the result)

Actions

  • Already tried to find a bug report, but could not find a matching COUNTIF bug report, though there are some related to that function.
  • Tested using OpenOffice 4.1.7 and got 0 for =COUNTIF(A2:A11;"<>0")

Question: Is this behavior intended and not a bug and if “yes” what do I miss?

Update According to @erAck’s comment, changed the example file and screenshot.
Update Incorrect version uploaded - deleted and correct version added.

COUNTIF-Question.ods

Geändertes Verhalten von Zählenwenn bei verbundenen Zellen ab LO Calc 6.1
Criteria to check if cell value is a date
#2

ODF 1.2 Part 2 4.11.8 Criterion:

For <>, if the value is not empty it matches any cell content except the value, including empty cells

(Note that value there is the value of the criterion, i.e. 0 in case of "<>0")

#3

Hmm … Criteron is the same for all functions / evaluations used. So I’d expect either 10 for all evaluations oder 0 for all evaluations. According the the cited ODF reference, I’d conclude that all other functions work incorrect. But anyway, need to take your answer as intended bahavior

#4

The "<>0" text Criterion is not the same as a comparison <>0 against numeric 0, in which also an empty cell is taken as 0, as with any other calculation in numeric context.

#5

@erAck - now I’m completely confused, since I cannot write something like =COUNTIF(A2:A11;<>0) or =COUNTIF(A2:A11;'<>0') to make it a numeric criterion. Please tell me how to make it a numeric criterion in COUNTIF

#6

I thought you were talking of the A2:A11<>0 part in the other formulas. No, a standalone <>0 or some such is not possible.

#7

Ok - may be I’ve prepared the use case too much. Just assume I want to count the number of cells having a numeric value <>0. The current implementation does not allow for this, while it does on earlier using exactly the same function as OpenOffice did. I’ll make a test with earlier release of LibreOffice where =COUNTIF(A2:A11;"<>0") exactly does accomplish this, since empty cells are not counted as matching the “<>0” criterion.

#8

See tdf#117433 for the context of behaviour change from old OOo-like behaviour to standard compliance. Note that the criterion cannot distinguish between numeric and textual data, so it needs to differentiate between cases of 0/"0" in a cell and an empty cell.

#9

@mikekaganski - thank you very much for getting the context of the behavior change. Your second sentence makes me feel an idiot, since I understand the part to the comma, but after the comma I don’t understand a single implication to the COUNTIF formula and it’s criterion (As far as I understood @erAck’s last comment it is not possible to make a just numeric criterion).

#10

I am sorry if I am unclear (English is not my native language, you know, so please excuse me). What I meant is that if empty cell would also be “equal to 0” in the criterion, then for “=0”, three cases would match: with 0 in the cell; with textual “0” in the cell; and with empty cell, so cells with text “0” would be equal to empty cells.

#11

Sorry @mikekaganski for insisting to satisfy my curiosity. I understand how it is implemented, that it is a requirement of 4.11.8 Criterion and that syntax definition of COUNTIF refers to 4.11.8 - so far, so good. If you want it turn that way: Now my problem is: Why doesn’t IF(REF<>0) evaluate to TRUE? Thus went to ODF specification website, searched for syntax definition of IF and found it using the term Logical Condition, for which I could not find such detailed information as for Criterion (in 4.11.8).

My conclusion: Logical Condition (as used in IF) is a different thing than Criterion (as used in COUNTIF) and (thus) there are different implementations in IF vs. COUNTIF.

#12

it wasn’t “term”, it was two terms: Logical (which is the name of a numeric subtype), and Condition, which was a name of the item in the definition, used in the following text to refer to that item.

You need the definition of operator <> to see which values it returns, but it doesn’t discuss the case of comparing empty values to 0.

But under 4.11.8 Criterion, there is “A reference to an empty cell is interpreted as the numeric value 0.” - no idea if that’s relevant here (reference as opposed to text discussed later).

#13

So the conclusion is right: Logical Condition is a different thing than Criterion?

#14

Condition in IF is a result of some evaluation having a logical (numerical) value. In case of using Calc’s <> operator, it compares a numerical 0 to a REF, which (in case of an empty cell) is treated as 0 when converted to a number.

Criterion is a value which will be somehow used later when doing actual comparisons, and it may be a reference (and then, if you simply pass REF as the criterion, you will have it equal to 0 in following comparisons), or it may be a text (and then the text is handled specially when it starts with some operator-like characters). So it’s apple-to-oranges to compare “Condition” to “Criterion”; and yes, textual criterion starting with = behaves in a specific way when applied to actual values, not as if those values were numerically compared to what is inside the quotes.

#15

So it’s apple-to-oranges to compare “Condition” to “Criterion”

That’s what I was looking for. Thank you so much for your patience.

#16

The "<>0" criterion does not test for empty cells, but cells not equal to content 0, which are all as none has content; to test for empty cells use "=", similar "<>" tests for non-empty cells.

See also the ODF OpenFormula (ODFF) specification. OpenOffice does not have that correctly implemented.

Btw, your =COUNT(IF(B2:B11)) does not do what you think it does. The IF(B2:B11) expression attempts to find an implicit intersection of the formula cell’s position B14 and the referenced range B1:B11, for which there is none, so it returns an error, which the COUNT() does not count and thus returns 0.

#17

I do not want to test for empty cells - I don’t want 'em to be counted in a COUNT for a condition (just stripped off the real problem),

#18

This?

=COUNTIF(A2:A11;"<>23")-COUNTIF(A2:A11;"=")
#19

@erAck - thank your for your efforts. As you can see, I have some solutions. I do not look for a solution or a specific formula but more an explanation and essentially it makes me feel quite uncomfortable to work out, that Criterion seems (! my personal feeling!) to have some kind of a function-dependent meaning - in IF it seems to have a different meaning than in COUNTIF - and essentially this difference is, what I try to understand.

The question is motivated by people having a formula like =COUNTIF(A2:A11;"<>0") within their sheets and now face a problem their sheets producing correct, but unexpected results. I got no doubt about the correct implementation of ODF specification but now I’ve to tell 'em: Rewrite your formulas.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.