What does it do?

It returns a value that you set if a condition is met, and a value if it is not met

Formula breakdown:

=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…

DOWNLOAD EXCEL WORKBOOK

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:

=IF(

IF Function Combined With AND Function

 

STEP 2: The IF arguments:

Logical_test

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.

=IF(AND(D15>3000, C15=”North”),

IF Function Combined With AND Function

Value_if_true

What value should be displayed if the condition is true?

We want “Bonus” to be displayed

=IF(AND(D15>3000, C15=”North”), “Bonus”,

IF Function Combined With AND Function

Value_if_false

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”)

IF Function Combined With AND Function

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

IF Function Combined With AND Function

You now have all of results!

IF Function Combined With AND Function

 

How to Use the Combined IF and AND Formula in Excel

If_And

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Excel Subtotal Function – Include Hidden Val... What does it do? It returns a Subtotal in a list or database Formula breakdown: =SUBTOTAL(function_num, ref1) What it means: =SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data) ***Go to the bottom...
In-Cell Bar Charts with the REPT Function When you are creating an Excel Dashboard and are limited by space and do not want to insert a chart, you can easily create an in-cell bar chart using the RPT (repeat) function. The RPT function uses the vertical bar character | as the first argument: text and references the va...
VLOOKUP Example: Vlookup with a Drop Down List What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP(thi...
Two Way Lookup Using The SUM Intersect Function There are various lookup functions that bring back values from a range of data like the VLOOKUP, INDEX, INDEX-MATCH and OFFSET functions, just to name a few. There is another lookup formula that you can use that will return the intersection of two Named Ranges! This is done...