countif does not recognize < and > symbols in excel cell values

Hi all, I am new at this so please bear with me. I'm trying to count cells that match certain criterias, everything works as expected when the cells are just numbers, but in some cells there are < and > signs before the numbers (i.e. >75) and I could not get it to work with countif. Anyone has any suggestions? Thanks alot!!

The syntax for > and < in Countif/sumif is

=COUNTIF(A:A,">"&B1)

Or are you saying that the > symbol is actually held in the reference cell..?

=COUNTIF(A:A,B1)
And B1 is >75

That's the same as writing
=COUNITF(A:A,">75")

That syntax works for me as well.

Thanks for your quick reply! However, the reference cell does in fact contain the > symbol, it is also the cell to be counted if this all works out. I tried both ways, with named range and ">75" in quotations but it still counts 0 for some reason... Any ideas? Thx again!

If the >75 is being held in a cell, then it should NOT be in quotes.

=COUNTIF(A:A,B1)
And B1 is >75 wihtout any quote marks in B1

Yes you are right, the > symbol is in the reference cell and I tried both ways above but both did not count...

I did =COUNTIF(coli, >75) but excel gave me an error...

Wait a second...

Take this sample countif
=COUNTIF(A1:A100,B1)
Which number(s) have the < or > symbols before them..the ones in A1:A100, or the one in B1 ?


It's probably time for you to post a sample book..

Originally Posted by chengg02

I did =COUNTIF(coli, >75) but excel gave me an error...

That needs to be written like
=COUNTIF(coli, ">75")

OK, I think I seemed to have found a solution... =COUNTIF(coli,"*75")
and it counted... would this be correct?

Thanks for your responses!!

Originally Posted by chengg02

OK, I think I seemed to have found a solution... =COUNTIF(coli,"*75")
and it counted... would this be correct?

I don't think that will count the cells that DON'T have the < symbol..