Countif with not equal <> sign not working

[yobejammin]

Hi,

I'm encountering an odd error with the Countif function and I haven't seen any other posts on this exact topic.

I have a very long list (96,906 entries) of txt IDs. A lot of these IDs are "-99" and I'm trying to use various Countifs formula on this list, and other columns in the sheet.

However, I'm getting a very odd error.

If I do =COUNTIF($L$2:$L$96907,"="&"-99") I get the expected result = 49,682
If I do =COUNTIF($L$2:$L$96907,"<>"&"-99") I get an unusual result = 96,902 (almost, but not quite the total number of items in the list).

I would expect to get the result 47,224 (96,906 minus 49,682), or you would at least think that excel would be consistent and in the first Countif case I'd get the output "4".

I can do the calculations another way to get the outputs I need, I just thought this was a very unusual error. I can't see a problem with the syntax I've used, and there are no problems with the list - no extra spaces, no special characters, no error cells.

I've tried this multiple ways - replacing the "-99" with a link to one of the cells with "-99" in it, copying and pasting the "-99" text into the formula etc and each time have reached the same unusual result.

Anyone have any idea what is going on?

Thanks,
Ben

 

[Jonmo1]

I don't know the 'why' part of this..

But I think the problem is that some of your -99's are not really numbers, but "Numbers stored as text"

Try copying a blank cell, highlighting column L, right click - paste special - values - Add - OK


The part I don't know the 'why' for is that
After some small testing, I've found that
"="&"-99" does count a text -99
but the
"<>"&"-99" Doesn't

 

[Jonmo1]

Also, this isn't 'wrong' persay, but
it's not necessary to do the concatenate of the = and -99, or <> and -99
you can just do
"=-99" or "<>-99"

And the = -99 doesn't need to be concatenated at all..
You can just do
=COUNTIF($L$2:$L$96907,-99)

You only need to concatenate the criteria when doing > or < or <>

 

[BiocideJ]

Strange.

I believe these formulas will give you what you want though (regardless of text/non-text)
=COUNTIF($L$2:$L$24,"-99")
=COUNTIF($L$2:$L$24,">-99")+COUNTIF($L$2:$L$24,"<-99")

 

[Juppu]

Hi,

I'm encountering an odd error with the Countif function and I haven't seen any other posts on this exact topic.

I have a very long list (96,906 entries) of txt IDs. A lot of these IDs are "-99" and I'm trying to use various Countifs formula on this list, and other columns in the sheet.

However, I'm getting a very odd error.

If I do =COUNTIF($L$2:$L$96907,"="&"-99") I get the expected result = 49,682
If I do =COUNTIF($L$2:$L$96907,"<>"&"-99") I get an unusual result = 96,902 (almost, but not quite the total number of items in the list).

I would expect to get the result 47,224 (96,906 minus 49,682), or you would at least think that excel would be consistent and in the first Countif case I'd get the output "4".

I can do the calculations another way to get the outputs I need, I just thought this was a very unusual error. I can't see a problem with the syntax I've used, and there are no problems with the list - no extra spaces, no special characters, no error cells.

I've tried this multiple ways - replacing the "-99" with a link to one of the cells with "-99" in it, copying and pasting the "-99" text into the formula etc and each time have reached the same unusual result.

Anyone have any idea what is going on?

Thanks,
Ben

I found a workaround COUNTIF does not "see" a number stored as text and a number

Use a * sign in the formular, and excel wont force convert text to numbers