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

#### gheyman

##### Well-known Member
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 ("")?

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Eric W

##### MrExcel MVP
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.")

Last edited:

#### gheyman

##### Well-known Member
Thank you. I thought the quotations would get around that. Very much appreciate your time and knowledge

Replies
2
Views
307
Replies
1
Views
29
Replies
3
Views
284
Replies
8
Views
71
Replies
7
Views
107