Countif for "greater than" operator returns wrong result (Excel 2013)

Question

I'm trying to count something that's categorized to 4 values. Let's say those values are:

1. less than or equal to 1
   
2. 1.1 to 2
   
3. 2.1 to 3
   
4. greater than 3

If I have this data inputted in A1-A8 excel cells:

≤ 1
≤ 1
1.1 to 2
1.1 to 2
2.1 to 3
2.1 to 3
> 3
> 3

And I collect only the unique values in B1-B4 excel cells:

≤ 1
1.1 to 2
2.1 to 3
> 3

All those cells are in "General" format already and have TRUE value in =istext() function

I used countif functions this way:

=COUNTIF($A$1:$A$8;B1)
=COUNTIF($A$1:$A$8;B2)
=COUNTIF($A$1:$A$8;B3)
=COUNTIF($A$1:$A$8;B4)

I expected the result to be:

2
2
2
2

As I have two "> 3" in my data

but the actual result returned these values:

2
2
2
0

can anybody help me with this?
thank you in advance! :)

Answer 1

You can also do this

=COUNTIF($A$1:$A$8,"="&B1)

See this question

Answer 2

As Solar Mike says, this is because > is treated as a special comparison for numbers.

To bodge a conversion to text, prepend it with an asterisk in your COUNTIF, like this:

=COUNTIF($A$1:$A$8; "*" & B4)

This will look for any text that ends in "> 3", so would also pick up things like "This is > 3" (but not "Is this > 3?", because it has text after the "3")

Answer 3

The countif() is not recognizing the "<=" or ">" while doing its evaluation or the cell A1 is trying to evaluate the "<= 1" and not succeeding.

I suggest using "LT=" or "GT" and then it works: