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?

 

[Aladin Akyurek]

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

An additional entry like 0588 would thwart that solution.


=SUM(COUNTIF(A2:A4,{"<>","058"})*{1,-1})

is an option for circumventing this erroneous behavior.

 

[jmacleary]

You misunderstand me. Try replacing 0504 in your sample data with 0583 and your count will go down to 1.

 

[6diegodiego9]

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

Thanks for the explanation.
The "problem" is that SUMPRODUCT in the help page is designed to do a totally different thing that returning "an array of True/False":
It's designed to do this: "Multiplies corresponding components in the given arrays, and returns the sum of those products."

Therefore it would be difficult for other to understand it...

 

[6diegodiego9]

You misunderstand me. Try replacing 0504 in your sample data with 0583 and your count will go down to 1.

I tried and I still get 2

 

[jmacleary]

I tried and I still get 2

Unfortunately I don't. Maybe its to do with the cell formatting. You do, however have at least 3 workable answers suggested (including mine), so I should go with one of them.

 

[Aladin Akyurek]

Did you also try the suggestion in post #11 ?

 

[6diegodiego9]

An additional entry like 0588 would thwart that solution

0588 is different than 058 so it must be counted, like 1234 or 9999.
Why do you write that it would thwart my solution?

 

[Aladin Akyurek]

0588 is different than 058 so it must be counted, like 1234 or 9999.
Why do you write that it would thwart my solution?

My set up does just that...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]code[/td][td][/td][td]

3​

[/td][/tr]
[tr][td]

2​

[/td][td]058[/td][td][/td][td]

4​

[/td][/tr]
[tr][td]

3​

[/td][td]058[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]03102[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]0588[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]0504[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

C1 >> (yours)

=COUNTIF(A2:A7,"<>*058*")

C2 >> the set up I suggested...

=SUM(COUNTIF(A2:A7,{"<>","058"})*{1,-1})

 

[jmacleary]

0588 is different than 058 so it must be counted, like 1234 or 9999.

This is in direct agreement with aladin's (and my) formula and your suggestion does NOT produce the result we think you are after. See aladin's post above for proof. By the way, my suggestion was

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

 

[6diegodiego9]

I apologize with everyone when I assumed that my formula counted 0588 as different, it didn't! sorry sorry sorry.

I also found that the solution with SUMPRODUCT don't work correctly when the numbers are stored as numbers.

=SUM(COUNTIF(A2:A4,{"<>","058"})*{1,-1}) instead works with numbers stored as text and as numbers as well.

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

Great solutions!
Thanks guys!!