Excel Tip: Applying multiple criteria in an IF formula

This week's post mostly applies to using the IF function, so if you don't already know how to use this incredibly powerful function, I would recommend that you read this post from 2010, which is still one of my most popular posts every month:

EXCEL TIP: The IF Statement made simple

If you already know how to use the IF function but want to apply multiple criteria, read on.

If you wish to apply more than one condition to your IF statement then you need to decide how you want to combine the multiple criteria. The two main options are:

1. All conditions must be true - this uses the AND function
2. Any of the conditions can be true - this uses the OR function

So, let's say the following cells are populated as below:

A1="Red"

A2=55

A3=400

So,

=AND(A1="Red",A2>30,A3<500)

will return TRUE as all conditions individually would return TRUE, whereas

=AND(A1="Red",A2<30)

will return FALSE, because one of the conditions would return FALSE.

However,

=OR(A1="Red",A2>30,A3<500)

will return TRUE as at least one condition individually would return TRUE, but

=OR(A1="Red",A2<30)

will also return TRUE, for the same reason.

You can create combinations of the two as well, by use an AND function as one of the arguments in an OR function, or vice versa.

So, for example:

=AND(A1="Red",OR(A2=55,A3=27))

works as follows, A1 must equal "Red" AND at least one of A2=55 or A3=27 must be true.

In this case it would return TRUE, whereas

=AND(A1="Blue",OR(A2=55,A3=400))

would return FALSE because A1="Blue" isn't true.

You can actually create quite complex rules using these two functions.

These do not, however, allow you to apply different criteria based on the results of other criteria. For that you need nested IF functions, which I will cover in a future post - maybe even next week.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".