Countif equals and not equals not working correctly

[sunrise06]

I have a column of text values that appear to be numeric and need to remain as text values. When I use this formula =COUNTIF($BH$5:$BH$16,"00") I get the correct result, but if I use the following formula I do no, =COUNTIF($BH$5:$BH$18"<>00").

However, if the values are changed to alpha characters, it works fine - e.g., =COUNTIF($BG$5:$BG$16,"F") or =COUNTIF($BG$5:$BG$16,"<>F")

Any ideas why that is?

 

[sunrise06]

Fluff, thank you, very interesting. Can you please explain how this works? I don't understand why SUMPRODUCT works in this situation and what the -- does.

THANKS!

 

[jtakw]

Did you SKIP thru my Post # 9 ?

 

[sunrise06]

Apparently, I did, sorry. Are you able to explain the formula?

 

[Fluff]

I have no idea why CountIf doesn't work, but the Sumproduct returns either true or false, which the -- then coerces into 1 (true) or 0 (false), which is then added together.

Have a look here for more info https://exceljet.net/excel-functions/excel-sumproduct-function

 

[sunrise06]

Awesome, thanks so much!

 

[Rick Rothstein]

This appears to work (note the asterisk)...

=COUNTIF(A1:A7,"<>00*")

While I do not know why it doesn't happen for the equal condition, I think for the not equal condition, the 00 is being converted to the number 0 for the comparison (you can see this by making one of the values a true 0 value... is will not be counted)... adding the asterisk seem to confirm to Excel that the 00 is really a text string.

 

[Rick Rothstein]

This appears to work (note the asterisk)...

=COUNTIF(A1:A7,"<>00*")

While I do not know why it doesn't happen for the equal condition, I think for the not equal condition, the 00 is being converted to the number 0 for the comparison (you can see this by making one of the values a true 0 value... is will not be counted)... adding the asterisk seem to confirm to Excel that the 00 is really a text string.

I should point out (not that it would matter for the OP's data), that beside not counting 00, my formula would also not count any text string that started with 00, so it is not a universal solution to this particular COUNTIF problem, only a solution for the OP's data possibilities.

 

[jtakw]

Apparently, I did, sorry. Are you able to explain the formula?

Yes I can, but seeing that you didn't like my formula, you probably wouldn't like my explanation neither.

 

[sunrise06]

Lol

 

[Rick Rothstein]

Lol

Just wondering if you saw the formula and explanation I posted in Message #16 and the follow up comment I added in Message #17 yet?