countif doesn't return the correct result when using "<>"

[6diegodiego9]

Take this table:

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]code[/TD]
[/TR]
[TR]
[TD="class: xl65"]058[/TD]
[/TR]
[TR]
[TD="class: xl65"]03102[/TD]
[/TR]
[TR]
[TD="class: xl65"]0504[/TD]
[/TR]
</tbody>[/TABLE]

where the "numbers" are stored as text to preserve the leading zero(es).

The formula =COUNTIF(A2:A4;"=058") returns 1.

Why does the formula =COUNTIF(A2:A4;"<>058") returns 3 instead than 2?

 

[Special-K99]

Weird

=COUNTIF(A2:A4,"=58") also returns 1 so it's seeing "058" as a number

=COUNTIF(A2:A4,"<>58") returns 3 ...and now it's not ??

Ah...
according to this

https://www.pcreview.co.uk/threads/countif-function-giving-incorrect-answer.3993503/

COUNTIF sees text and numbers the same

The last post on that page talks of switiching to the 1904 date and the results changing.

 

[wideboydixon]

I think it's an anomaly of the COUNTIF function. See this article:

https://groups.google.com/forum/#!m....worksheet.functions/1lK458gIGFY/DAHBBVYdNZEJ

You could re-write as this perhaps:

=SUMPRODUCT(--(A2:A4<>"058"))

WBD

 

[Special-K99]

Since you know the number of rows in the range you could always use

=3-COUNTIF(A2:A4,"=058")

 

[jmacleary]

Or this formula should work:

=COUNTA(A2:A4)-COUNTIF(A2:A4,"=058")

 

[6diegodiego9]

Thanks for the answers!
As I don't know the number of rows in real case I found that the minimalist working "solution" is this:

=COUNTIF(A2:A4,"<>*058*") (it returns 2)


In real case with many more values in the source table, can you imagine a case where "<>*058*" is not equivalent to (a correctly-working) "<>058"?

 

[6diegodiego9]

=SUMPRODUCT(--(A2:A4<>"058")) also works but it's hard to understand for me...

 

[jmacleary]

Unfortunately yes. Any cell with 058 as part of it will be matched (e.g. 2058, 0058,0583, etc. Try my suggestion above.

 

[wideboydixon]

=SUMPRODUCT(--(A2:A4<>"058")) also works but it's hard to understand for me...

A2:A4<>"058" returns an array of True/False like this:

{False, True, True}

Unfortunately, True equates to -1 so the --() around it changes the "sign" of the values to get this:

{0, 1, 1}

You're then just summing up these values which returns 2.

You could also do it like this:

=SUM(--(A2:A4<>"058"))

But you'd have to use Ctrl+Shift+Enter to create an array formula for this.

WBD

 

[6diegodiego9]

Unfortunately yes. Any cell with 058 as part of it will be matched (e.g. 2058, 0058,0583, etc. Try my suggestion above.

and this is good, as it must count all the values that are not (exactly) "058".

So again: can you imagine a case where "<>*058*" is not equivalent to (a correctly-working) "<>058"?