How to Use IF with AND Function in Excel (Formula + VBA)

We can accomplish a lot of complicated tasks using the combination of the IF with AND functions. If you are curious to know how you can use the IF with AND function to form formulas to do different tasks in Excel, then this article may come in handy for you. In this article, we discuss how you use the IF with AND function to form formulas to do different tasks in Excel with elaborate explanations.


Download Practice Workbook

Download this practice workbook below.


5 Examples to Use IF with AND Function in an Excel Formula

For the demonstration purpose, we are going to use the below dataset. Try to use the Excel 365 version to avoid any version or compatibility issues.

how to use IF AND function in excel


1. Use of IF with AND Function for Blanks

Here, we are going to use the IF with AND functions together. To filter out values that are not blank and in the Asia region.

Steps

  • Select cell G5 and enter the following formula:

=IF(AND(B5="Asia",C5<>""), "PASSED","DISQUALIFIED")

Use of IF with AND Function for Blanks in Excel.

Formula Breakdown

AND(B5=”Asia”,C5<>””)

⮚ It will check the following two criteria,

  • Whether the value is in the Asia region.
  • And corresponding quantity values are blank or not.

If both of these criteria are met, then it will return logical true.

IF(AND(B5=”Asia”,C5<>””), “PASSED”,”DISQUALIFIED”)

⮚ This final line of code will check the return from the first argument is TRUE. If it is, then the output will show  PASSED. if mentioned criteria are not met, then it will show DISQUALIFIED.

  • Then drag the Fill Handle to cell G13.
  • Now we can observe that the range of cells G5:G13 is now with comments regarding whether it is passed or not.

And this is how we use the IF with AND function in Excel to identify blanks and separate them.

Read More: How to Return TRUE or FALSE Using Excel AND Function


2. IF-AND Formula with 'Greater Than' and 'Less Than' Operator

Here, we are going to use the IF with AND functions together. To filter out values that have profit values greater than $1200 and quantity less than 70.

Steps

  • Select cell G5 and enter the following formula:

=IF(AND(F5>1200,C5<70), "PASSED","DISQUALIFIED")

IF-AND Formula with 'Greater Than' and 'Less Than’ Operator in Excel

Formula Breakdown

AND(F5>1200,C5<70)

⮚ It will check the following two criteria,

  • Whether the value in cell F5 is greater than $1200.
  • And corresponding quantity values are less than 70 or not.

If both of these criteria are met, then it will return logical true.

IF(AND(F5>1200,C5<70), “PASSED”,”DISQUALIFIED”)

⮚ This final line of code will check the return from the first argument is TRUE. If it is, then the output will show PASSED. if mentioned criteria are not met, then it will show DISQUALIFIED.

  • Then drag the Fill Handle to cell G13.
  • Now we can observe that the range of cells G5:G13 is now filled with comments regarding whether it is passed or not.

And this is how we use the IF with AND function in Excel with the greater than and less than operators.


3. Calculate Commission Combining IF with AND Function

Here, we are going to use the IF with AND functions combined to determine how much commission they would get. If the value has a profit greater than $1200, and a quantity less than 70, the formula will return 10%. Otherwise, it won’t return any commission.

Steps

  • Select cell G5 and enter the following formula:

=IF(AND(F5>1200,C5<70),F5*10%,"No Commission")

Calculate Commission Combining IF with AND Function in Excel

Formula Breakdown

AND(F5>1200,C5<70)

⮚ It will check the following two criteria,

  • Whether the value has a Profit that is greater than $1200.
  • And corresponding quantity values are less than 70.

If both criteria are met, then it will return logical true.

IF(AND(F5>1200,C5<70),F5*10%,”No Commission”)

⮚ This final line of code will check the return from the first argument is TRUE. If it is, then the output will return a 10% value of the profit. if mentioned criteria are not met, then it will show No Commission.

  • Then drag the Fill Handle to cell G13.
  • Now we can observe that the range of cells G5:G13 is now filled with comments regarding whether it is passed or not.


4. IF-AND Formula with Multiple Conditions

Here, we are going to use the IF with AND functions together. To filter out values that are in the Asia region, quantity values are not Blank, and the Profit value is greater than $1200.

If all three criteria are met, the output will show PASSED. Otherwise, it will show DISQUALIFIED.

Steps

  • Select cell G5 and enter the following formula:

=IF(AND(B5="Asia",C5<>"",F5>1200), "PASSED","DISQUALIFIED")

IF-AND function with Multiple Conditions

Formula Breakdown

AND(B5=”Asia”,C5<>””,F5>1200)

⮚ It will check the following two criteria,

  • Whether the value is in the Asia region.
  • And corresponding quantity value is Blank.

If both of these criteria are met, then it will return logical true.

IF(AND(B5=”Asia”,C5<>””,F5>1200), “PASSED”,”DISQUALIFIED”)

⮚ This final line of code will check the return from the first argument is TRUE. If it is, then the output will show  PASSED. If mentioned criteria are not met, then it will show DISQUALIFIED.

  • Then drag the Fill Handle to cell G13.
  • Now we can observe that the range of cells G5:G13 is now filled with comments regarding whether it is passed or not.

Read More: How to Use IFS and AND Functions Together in Excel (3 Examples)


5. Using Nested IF-AND Formula

Here, we are going to use the IF with AND functions together. To filter out values that meet these three separate criteria.

  • Whether the value is in Asia
  • And corresponding quantity value is Blank.

If all three criteria are met, the output will show PASSED.

If not, then it checks for the second set of criteria,

  • If the Region values are not Asia region.
  • Whether the Quantity is greater than 50 or not.

Finally, if they satisfy both of the criteria, then the output would be Average.

Otherwise, if none set of the mentioned criteria is met, then it will show DISQUALIFIED.

Steps

  • Select cell G5 and enter the following formula:

=IF(AND(B5="Asia",C5<>""),"PASSED",IF(AND(B5<>"Asia",C5>50),"Average","DISQUALIFIED"))

Using Nested IF-AND Function in Excel

Formula Breakdown

AND(B5=”Asia”,C5<>””)

  • Whether the value is in Asia
  • And corresponding quantity value is Blank.

AND(B5<>”Asia”,C5>50)

If not, then it checks for the second set of criteria,

  • If the Region value is not Asia region.
  • Whether the Quantity is greater than 50

IF(AND(B5=”Asia”,C5<>””),”PASSED”,IF(AND(B5<>”Asia”,C5>50),”Average”,”DISQUALIFIED”))

⮚ This final line of code will check two sets of criteria. If the first set of criteria is met, the output will show PASSED. If the second set of these criteria is met, then the output would be Average. Finally, if none set of the mentioned criteria matches, then it will show DISQUALIFIED.

  • Then drag the Fill Handle to cell G13.
  • Now we can observe that the range of cells G5:G13 is now f with comments regarding whether it is passed or not.

Read More: Nested IF and AND Functions in Excel (4 Suitable Examples)


2 Examples with Excel VBA to Use If with And Function

In the following sections, we are going to provide you with 2 examples with Excel VBA to combine If with And for multiple conditions. We recommend you learn and apply these methods to your Excel worksheet. It will surely develop your Excel knowledge.


1. Combining If with And for Two Conditions

In this example, you will learn to combine If with And for multiple conditions using the VBA code.

Now, our goal is to give them a discount based on the Amount and Status. There will be two conditions here:

  • If the amount is more than $450 and the status is “VIP”, give them a 5% discount.
  • Otherwise, there will be no discount.

Steps

  • First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

Combining If with And function for Two Conditions

  • Then there will be a new dialog box. In that dialog box, click on Insert > Module.

inserting module

  • Next, in the Module editor window, type the following code:
Sub discount_amount()
Dim cell, output_rng As Range
Set output_rng = Range("E5:E9")
For Each cell In output_rng
    If cell.Offset(0, -2) > 450 And cell.Offset(0, -1) = "VIP" Then
        cell.Value = "10% Discount"
    Else
        cell.Value = "No Discount"
    End If
Next
End Sub
  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros.

  • After clicking View Macros, select the macros that you created just now. The file name here is discount_amount. Then click Run.

  • As you can see, we successfully used multiple conditions combining If with And in Excel VBA. Give this a try.

Read More: How to Use Conditional Formatting with AND Function in Excel


2. Utilizing More Than Two Conditions in Excel VBA

In this example, we will combine three conditions in a single If-And statement in VBA.

The three conditions are,

  • If someone joined before January 20, is age more than 30, and current salary of less than $5000, give them a 10% increment.
  • Otherwise, give them a 5% increment.

Steps

  • First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’for opening the Visual Basic Editor.

Utilizing More Than Two Conditions in Excel VBA

  • Then there will be a new dialog box. In that dialog box, click on Insert > Module.

insert module

  • Next, in the Module editor window, type the following code
Sub increment()
Dim cell, output_rng As Range
Set output_rng = Range("F5:F12")
For Each cell In output_rng
    If cell.Offset(0, -3) < #1/1/2020# _
    And cell.Offset(0, -2) > 30 _
    And cell.Offset(0, -1) < 5000 _
    Then
        cell.Value = "10%"
    Else
        cell.Value = "5.5%"
    End If
Next
End Sub

  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros.

  • After clicking View Macros, select the macros that you created just now. The file name here is Increment. Then click Run.

  • As you can see, only three employees met the conditions to get a 10% increment and the rest of them are getting a 5.5% increment. So, here we also successfully used multiple conditions combining If with And using Excel VBA. In this way, you can add more conditions to an If statement in VBA.


Conclusion

To sum it up, how you can use IF with AND function to form formulas to do different tasks in Excel is answered here by different examples.

For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo