Using IF with AND, OR and NOT functions
The IF function allows you to make a logical
comparison between a value and what you
expect by testing for a condition and returning a
result if that condition is True or False.
=IF(Something is True, then do something,
otherwise do something else)
But what if you need to test multiple conditions,
where let’s say all conditions need to be True or
False (AND), or only one condition needs to be
True or False (OR), or if you want to check if a
condition does NOT meet your criteria? All 3
functions can be used on their own, but it’s
much more common to see them paired with IF
functions.
Here are overviews of how to structure AND, OR
and NOT functions individually. When you
combine each one of them with an IF statement,
they read like this:
AND – =IF(AND(Something is True, Something
else is True), Value if True, Value if False)
OR – =IF(OR(Something is True, Something
else is True), Value if True, Value if False)
NOT – =IF(NOT(Something is True), Value if
True, Value if False)
Examples
Following are examples of some common
nested IF(AND()), IF(OR()) and IF(NOT())
statements. The AND and OR functions can
support up to 255 individual conditions, but
it’s not good practice to use more than a few
because complex, nested formulas can get
very difficult to build, test and maintain. The
NOT function only takes one condition.
Formula Description
=IF(AND(A2>0,B2<100),TRUE, FALSE) IF A2 (25) is greater than 0, AND
B2 (75) is less than 100, then
return TRUE, otherwise return
FALSE. In this case both
conditions are true, so TRUE is
returned.
=IF(AND(A3="Red",B3="Green"),TRUE, If A3 (“Blue”) = “Red”, AND B3
FALSE) (“Green”) equals “Green” then
return TRUE, otherwise return
FALSE. In this case only the first
Formula Description
condition is true, so FALSE is
returned.
=IF(OR(A4>0,B4<50),TRUE, FALSE) IF A4 (25) is greater than 0, OR
B4 (75) is less than 50, then
return TRUE, otherwise return
FALSE. In this case, only the first
condition is TRUE, but since OR
only requires one argument to
be true the formula returns
TRUE.
=IF(OR(A5="Red",B5="Green"),TRUE,F IF A5 (“Blue”) equals “Red”, OR
ALSE) B5 (“Green”) equals “Green”
then return TRUE, otherwise
return FALSE. In this case, the
second argument is True, so the
formula returns TRUE.
=IF(NOT(A6>50),TRUE,FALSE) IF A6 (25) is NOT greater than
50, then return TRUE, otherwise
return FALSE. In this case 25 is
not greater than 50, so the
formula returns TRUE.
You have some sales numbers in column B and you
are requested to flag the amounts greater than $50
but less than $100. To have it done, insert this
formula in C2 and then copy it down the column:
To process some other boundary values without
changing the formula, enter the minimum and
maximum numbers in two separate cells and refer to
those cells in your formula. For the formula to work
correctly in all the rows, be sure to use for the
boundary cells ($F$1 and $F$2 in our case):