Countif Formula Problem ("< 1 Yr Exp.")

[gheyman]

My countIf formula doesn't see to like "< 1 Yr Exp." If I change the data where its looking to lets say "Test" and I change the formula as well, it works fine. so it has to be that I'm using "< 1 Yr Exp." in the formula.

< 1 Yr Exp. = (Less then 1 Year expired)

=COUNTIFS('CMOP TABLE'!$CF:$CF,$B1,'CMOP TABLE'!$CI:$CI,"< 1 Yr Exp.")

Why does using "< 1 Yr Exp." in my formula cause a problem I am using quotations ("")?

 

[Eric W]

Because the COUNTIF type functions include the operator as part of the string. So when it sees the < sign in the first position, it assumes that you want to use that as an operator, not a text value. You can get around that by giving it another operator first, like an = sign, like this:

=COUNTIFS('CMOP TABLE'!$CF:$CF,$B1,'CMOP TABLE'!$CI:$CI,"="&"< 1 Yr Exp.")

or just

=COUNTIFS('CMOP TABLE'!$CF:$CF,$B1,'CMOP TABLE'!$CI:$CI,"=< 1 Yr Exp.")

 

[gheyman]

Thank you. I thought the quotations would get around that. Very much appreciate your time and knowledge

 

[Eric W]

Glad to help.