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.

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:

• 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:

• 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:

• 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:

• 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:

• 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:

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.

• Then there will be a new dialog box. In that dialog box, click on Insert > 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.

• Then there will be a new dialog box. In that dialog box, click on 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

• How to Use AND Function with Text in Excel (4 Simple Methods)
• How to Use SUMIF and AND Function in Excel (2 Easy Methods)