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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
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 ("")?
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,212
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
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
Thank you. I thought the quotations would get around that. Very much appreciate your time and knowledge
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,989
Members
409,553
Latest member
alscno

This Week's Hot Topics

Top