What does it do?
It returns a value that you set if a condition is met, and a value if it is not met
=IF(AND(Logical Test),Value if True,Value if False)
What it means:
=IF((Sales are bigger than $3000 & in the North region),“Bonus”,“No Bonus”)
When combining (or nesting) the AND function with the IF function, it allows you to add more than one condition to your formula, something that is not possible with the IF function by itself.
So you can show the results of Sales Reps that have made more than $3,000 of sales AND who are part of the North region, as explained below…
We want to show a Bonus value if sales are bigger than $3000 and comes from the North region, and No Bonus is shown if this condition is not met.
STEP 1: We need to enter the IF function in a blank cell:
STEP 2: The IF arguments:
What is your condition?
Sales Rep has sold more than 3000 dollars and comes from the North Region. Let us use the AND function to accomplish this.
What value should be displayed if the condition is true?
We want “Bonus” to be displayed
=IF(AND(D15>3000, C15=”North”), “Bonus”,
What value should be displayed if the condition is not met?
We want “No Bonus” to be displayed
=IF(AND(D15>3000, C15=”North”), “Bonus”, “No Bonus”)
Apply the same formula to the rest of the cells by dragging the lower right corner downwards.
You now have all of results!
How to Use the Combined IF and AND Formula in Excel