Ask Your Question
0

Function COUNTIF with Empty Cells

asked 2019-10-28 17:53:39 +0200

Opaque gravatar image

updated 2019-11-29 15:58:46 +0200

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.

C:\fakepath\COUNTIF-Question.ods

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2019-10-28 18:09:47 +0200

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")

edit flag offensive delete link more

Comments

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

Opaque gravatar imageOpaque ( 2019-10-28 18:16:44 +0200 )edit

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.

erAck gravatar imageerAck ( 2019-10-28 18:29:10 +0200 )edit

@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

Opaque gravatar imageOpaque ( 2019-10-28 18:39:59 +0200 )edit

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.

erAck gravatar imageerAck ( 2019-10-28 18:46:53 +0200 )edit

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.

Opaque gravatar imageOpaque ( 2019-10-28 19:03:55 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-10-28 19:10:41 +0200 )edit

@Mike Kaganski - 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).

Opaque gravatar imageOpaque ( 2019-10-28 19:55:33 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-10-28 20:01:33 +0200 )edit

Sorry @Mike Kaganski 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.

Opaque gravatar imageOpaque ( 2019-10-29 10:57:42 +0200 )edit

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).

Mike Kaganski gravatar imageMike Kaganski ( 2019-10-29 11:29:38 +0200 )edit
0

answered 2019-10-28 18:24:42 +0200

erAck gravatar image

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.

edit flag offensive delete link more

Comments

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),

Opaque gravatar imageOpaque ( 2019-10-28 18:29:31 +0200 )edit

This?

=COUNTIF(A2:A11;"<>23")-COUNTIF(A2:A11;"=")
erAck gravatar imageerAck ( 2019-10-28 18:53:11 +0200 )edit

@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.

Opaque gravatar imageOpaque ( 2019-10-28 19:09:16 +0200 )edit
Login/Signup to Answer

Question Tools

Stats

Asked: 2019-10-28 17:53:39 +0200

Seen: 3,118 times

Last updated: Nov 21 '19