IF with AND in an Excel Formula (7 Examples)

In Microsoft Excel, combining multiple functions to get output is everyday work. We use various operators to extract a particular result from a formula. The IF function and AND function are one of them. Incorporating IF with AND works like magic. In this tutorial, you will learn to combine IF with AND in an Excel Formula with suitable examples and proper illustrations.

Download Practice Workbook

Download this practice workbook.

7 Examples of Combining IF with AND in an Excel Formula

Now, we all know the IF function basically operates based on the value that the statement return. The IF function can have only one condition in it. To implement multiple criteria, we can take the help of the AND function. It is a logical operator. It will also return TRUE or FALSE based on the statement. This TRUE or FALSE will pass on the IF condition. After that, the IF function will evaluate something based on these results.

In the following sections, we will provide you with seven examples of IF with AND in an Excel formula. We recommend you read and learn all of these. It will surely improve your Excel knowledge.

1. Greater than x But Less than y with IF & AND Function

In this example, our AND function will contain multiple conditions greater than or less than the operator. We will show you three instances here in this example.

The first instance is this:

Your budget is between $500 to $1000 to buy a TV. A price less than $500 won’t be good. Also, you don’t want to cross $1000. In this case, you can use the IF and AND find it.

To get this, we are using this formula:

As you can see, the formula successfully shows which one to buy based on the statement.

Second Instance:

You can also perform this with the “Greater than or equal (>=)” and “Less than or equal (<=)” operators.

Just a simple tweak in the formula:

Third Instance:

Now, you can perform the same thing with absolute cell references. Take a look at the following screenshot to have a better understanding.

Formula:

As you can see, we have used the absolute cell references to perform the operation with IF and AND functions.

Read More: Excel VBA: Combining If with And for Multiple Conditions

2. Formula with IF and AND Function: Compute Something

Now, apart from returning a particular value, the IF function can also perform calculations in it. The estimations will depend on the return value TRUE or FALSE. But, this time IF function will also contain the AND function.

Take a look at the following dataset:

Here, the dataset demonstrates some employees’ salary, their serving years, and the number of their completed projects. Now, we will increment their salaries. The condition is: “If someone is serving more than 4 years and completed more than 10 projects, increase their salary by 10%. Otherwise, increase their salary by 6%.”

📌 Steps

• First, type the following formula in Cell F5:

• After that, press Enter and drag the Fill handle icon over the range of cells F6:F10.

As you can see from the result, our IF-AND formula successfully returns the desired value based on the conditions.

3. Formula Using IF Function with Multiple Conditions in AND

From the previous examples, you noticed that we used only two conditions in the AND function. But, you can use more than two statements in an IF AND formula. But there are some limitations to this:

• You can use up to 255 arguments in a formula in Excel 2019 – 2007. The length should not cross more than 8192 characters.
• In previous versions (Excel 2003 and lower), you can’t use more than 30 statements. The total length can not exceed 1,024 characters.

Take a look at the following dataset:

Here, we have a dataset of some orders from the customers. Now, we want to give them some discount based on some conditions.

The condition is: “If any customer is in VIP category, No. of purchase more than 5 and buying with a price more than $400, give them a discount”.

📌 Steps

• Now, type the following formula in Cell F5:

• Then, press Enter and drag the Fill handle icon over the range of cells F6:F10.

As you can see, our formula returns the exact value. So, we successfully used the IF and AND functions for an Excel formula.

Read More: VBA IF Statement with Multiple Conditions in Excel (8 Methods)

4. Using Nested IF-AND in an Excel Formula

In this example, we will combine multiple IF AND functions in the same formula. You can call it the nested IF AND formula.

Take a look at the following screenshot:

Here, we have some students’ attendance days and their marks. Now, the conditions are:

📌 Steps

• First, type the following formula in Cell E5:

• Next, press Enter and drag the Fill handle icon over the range of cells E6:E10.

As you can see, we successfully used multiple conditions in our IF-AND formula. The formula returns the exact results that we wanted.

Read More: Excel If Function with Multiple Conditions (Nested IF)

Similar Readings

• Excel VBA: If Then Else Statement with Multiple Conditions (5 Examples)
• Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)
• How to Use Multiple If Conditions in Excel for Aging (5 Methods)
• How to Use Multiple IF Condition in Excel (3 Examples)

5. Using IF AND Formula for Case-Sensitive Data

Now, the IF AND formula do not differentiate between uppercase and lowercase letters. It happens because the AND function is case-insensitive by default.

To solve this, we can use the EXACT function to make this formula a case-sensitive one. We will nest it with the AND function. If you are working on case-sensitive data, you should use this according to my opinion.

Take a look at the following dataset:

Here you can see some persons’ names and their serving years in a company. Also, you can see there are multiple persons named Robert. Our condition is: “ Find the match for name Robert with serving years of more than 15 years”.

📌 Steps

• Now, type the following formula in Cell D5:

• Then, press Enter and drag the Fill handle icon over the range of cells D6:D10.

As you can see from above, our IF AND formula find the exact match based on the condition.

6. IF AND Formula with a Date in Excel

If you have followed the previous methods, this example won’t be difficult for you. You can compare dates in the formula. In this example, we will use a date to demonstrate this problem.

Here, we have a dataset of some employees’ joining dates and the number of projects they have completed. Our condition is: “If someone joined before 1 January 2022 and completed projects more than 10, give them a promotion”.

📌 Steps

• First, type the following formula in Cell E5:

• Then, press Enter and drag the Fill handle icon over the range of cells E6:E10.

As you can see, both Stacy and Mike joined before 1 January 2022 and completed more than 10 projects. So, our formula successfully returns a value based on the conditions.

7. Combining IF AND with OR in an Excel Formula

We are not limited to using one logical operator in a formula. In fact, you can use multiple logical operators in an IF AND formula. It basically checks various combinations of your given conditions. These logical tests allow you to analyze complex criteria in a dataset.

To demonstrate this, we are using the OR function in our IF AND formula in Excel.

Here, we have some salespersons’ names, their selling products, and the selling prices. Now, our condition is: “Find a match where a salesperson sold TV or Laptop for more than $1500”.

📌 Steps

• Now, type the following formula in Cell E5:

• Next, press Enter and drag the Fill handle icon over the range of cells E6:E10.

Here you can see, our IF AND formula found two matches for the particular condition. In this way, you can use multiple logical operators in Microsoft Excel.

Read More: Excel VBA: Combined If and Or (3 Examples)

💬 Things to Remember

✎ The formula will show #VALUE! Error if it can not evaluate it with TRUE or FALSE.

✎ If you provide an array as an argument, the IF function will evaluate every element of that array.

✎ To count conditions, you can use the COUNTIF function or the COUNTIFS function.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the IF AND formula in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• Excel IF Function with 3 Conditions
• Excel If Statement with Multiple Conditions in Range (3 Suitable Cases)
• How to Use Multiple IF Statements with Text in Excel (6 Quick Methods)
• Excel IF between Multiple Ranges (4 Approaches)
• How to Use PERCENTILE with Multiple IF Condition in Excel (3 Examples)