SE AND in Excel: Nested Formula, Multiple Statements, and More (2023)

The tutorial shows how to use IF together with the AND function in Excel to check multiple conditions in a formula.

Some things in the world are finite. Others are infinite, and the IF function seems to be one of those things. On our blog, we already have a handful ofExcel IF Tutorialsand still discover new uses every day. Today we are going to see how you can use IF together with the AND function to evaluate two or more conditions at the same time.

• Excel SE E Formula
• IF greater than AND less than
• IF AND, then calculate
• Excel SE with multiple AND conditions
• IF nested AND in Excel
• IF AND function is case sensitive
• IF AND OR statements

IF AND statement in Excel

To construct the IF AND statement, you obviously need to combine theEyYfunctions in a formula. That's how:

YES IT IS(condition1,condition2,…), value_if_true, value_if_false)

Translated into plain English, the formula is as follows: IF condition 1 is true AND condition 2 is true, do one thing, otherwise do another.

As an example, let's make a formula that checks that B2 is "delivered" and C2 is not empty, and based on the results, do one of the following:

• If both conditions are TRUE, mark the order as "Closed".
• Se uma das condições for FALSE ou ambas forem FALSE, retorne uma string vazia ("").

=IF(E(B2="delivered", C2<>""), "Closed", "")

The following screenshot shows the IF AND function in Excel:

If you want to return some value if the logical test evaluates to FALSE, provide that value in thevalue_if_falseargument. For example:

=IF(E(B2="Delivered", C2<>""), "Closed", "Open")

The modified formula returns "Closed" if column B is "delivered" and C has some date (not blank). In all other cases, it returns "Open":

UseWhen using an IF AND formula in Excel to evaluate text conditions, remember that lowercase and uppercase letters are treated as the same character. If you are looking for oneCase sensitive IF E formula, wrap one or more AND arguments in the EXACT function as done in the linked example.

Now that you know the syntax of Excel's IF AND statement, let me show you what kinds of tasks it can solve.

Excel SE: greater than and less than

In the above example, we were testing two conditions on two different cells. But sometimes it may be necessary to run two or more tests on the same cell. A typical example is checking whether the value of a cell isbetween two numbers. Excel's IF AND function can easily do this too!

Suppose you have some sales figures in column B and you are asked to check for values ​​greater than $50 but less than $100. To do this, enter this formula in C2 and copy it into the column:

=SI(Y(B2>50, B2<100), "x", "")

If you need to include thelimit values(50 e 100), use oless than or equal tooperator (<=) andBetter than or equal to(>=) operator:

=SI(Y(B2>=50, B2<=100), "x", "")

To process some other threshold values ​​without changing the formula, enter the minimum and maximum numbers in two separate cells and refer to those cells in your formula. For the formula to work correctly on all rows, be sure to useabsolute referencesfor the boundary cells ($F$1 and $F$2 in our case):

=SI(Y(B2>=$F$1, B2<=$F$2), "x", "")

Using a similar formula, you can check whether ameetingcaiwithin a specified range.

For example, let's mark the dates between September 10, 2018 and September 30, 2018, inclusive. A minor hurdle is that dates cannot be provided directly to logic tests. For Excel to understand dates, they must be included in theFECHAVALOfunction, like this:

=SE(Y(B2>=VALUEDATE("10/9/2018"), B2<=VALUEDATE("30/9/2018")), "x", "")

Or just enterOfyANdates into two cells ($F$1 and $F$2 in this example) and "pull" them from those cells using the familiar IF AND formula:

=SI(Y(B2>=$F$1, B2<=$F$2), "x", "")

For more information, seeExcel IF statement between two numbers or dates.

IF this AND that then calculate something

In addition to returning predefined values, Excel's IF AND function can also perform different calculations depending on whether the specified conditions are TRUE or FALSE.

To demonstrate the approach, we'll calculate a 5% bonus for "closed" sales with an amount greater than or equal to $100.

Assuming the quantity is in column B and the order status is in column C, the formula is as follows:

=IF(E(B2>=100, C2="closed"), B2*10%, 0)

The above formula assigns zero to the remainder of orders (value_if_false= 0). If you are willing to give a small nurturing bonus, say 3%, for orders that do not meet the conditions, please include the corresponding equation in thevalue_if_falseargument:

=SI(Y(B2>=100, C2="cerrado"), B2*10%, B2*3%)

Multiple IF AND statements in Excel

As you may have noticed, we evaluated only two criteria in all of the examples above. But there's nothing to stop you from including three or more tests in your SE AND formulas, as long as they meet these general Excel limitations:

• In Excel 2007 and later, up to 255 arguments can be used in a formula, with a total formula length of no more than 8192 characters.
• In Excel 2003 and earlier, no more than 30 arguments are allowed, with a total length of no more than 1024 characters.

As an example of multiple AND conditions, consider these:

• The amount (B2) must be greater than or equal to $100
• Order status (C2) is "Closed"
• The delivery date (D2) is within the current month

Now we need an IF AND statement to identify orders for which all 3 conditions are TRUE. And here it is:

=IF(S(B2>=100, C2="Closed", MONTH(D2)=MONTH(TODAY())), "x", "")

Since the 'current month' at the time of this writing is October, the formula gives the following results:

Nested IF AND statements

When working with large spreadsheets, it may be necessary to check a few different sets of AND criteria at once. For this you take a classic.Excel Nested IF Formulaand extend your logic tests with AND statements, like this:

YES IT IS(…),output1, YES IT IS(…),salida2, YES IT IS(…),salida3,movie 4)))

To get a general idea, look at the following example.

Let's say you want to rate your service based on shipping cost and estimated delivery time (ETD):

• Great: shipping cost under $20 and ETD under 3 days
• Poor: shipping costs more than $30 and ETD more than 5 days
• Average: anything in between

To do this, write two individual IF AND statements:

IF(E(B2<20, C2<3), "Excellent",...)

SE(E(B2>30, C2>5), "Ruim",...)

...and snap one to the other:

=IF(S(B2>30, C2>5), "Poor", IF(S(B2<20, C2<3), "Excellent", "Average"))

The result will be similar to this:

More example formulas can be found atExcel nested IF AND statements.

Case sensitive IF function in Excel

As mentioned at the beginning of this tutorial, IF AND formulas in Excel are case insensitive because the AND function is case insensitive by nature.

If you are working withupper and lowercasedata and you want to evaluate the AND conditions considering the case of the text, perform each individual logic test inside the EXACT function and nest these functions in your AND statement:

IF(AND(EXACT(cell, "condition1"), EXACTLY(cell, "condition2")), value_if_true, value_if_false)

For this example, we'll flag orders from a specific customer (for example, the company namedcyberspace) with a value that exceeds a certain number, say $100.

As you can see in the screenshot below, some company names in column B have the same case extract and yet they are different companies so we need to check the names.exactly. The quantities in column C are numbers, and we perform a regular "greater than" test for them:

=IF(AND(EXACT(B2, "Cyberspace"), C2>100), "x", "")

To make the formula more flexible, you can enter the target customer's name and value in two separate cells and refer to those cells. Just rememberblock cell references with the $ sign($G$1 and $G$2 in our case) so they don't change when you copy the formula to other lines:

=IF(AND(EXACT(B2, $G$1), C2>$G$2), "x", "")

Now, you can type any name and value in the referenced cells, and the formula will mark the corresponding orders in your table:

IF OR AND Formula in Excel

In Excel SE formulas, you are not limited to using just onelogical function. To check various combinations of various conditions, you can combine IF, AND, OR and other functions to perform the necessary logical tests. Here is an example ofFormula IF AND ORwhich tests a pair of OR conditions inside AND. And now, I'll show you how you can do two or more AND tests inside the OR function.

Suppose you want to mark orders for two customers with an amount greater than a certain number, say $100.

In the Excel language, our conditions are expressed like this:

OR AND(Cliente1,The amount>100), Y(Cliente2,The amount>100)

Assuming that customer names are in column B, quantities are in column C, the 2 target names are in G1 and G2, and the target quantity is in G3, use this formula to mark the corresponding orders with "x":

=SI(O(Y(B2=$G$1, C2>$G$3), Y(B2=$G$2, C2>$G$3)), "x", "")

The same results can be achieved with a more compact syntax:

=SI(Y(O(B2=$G$1,B2= $G$2), C2>$G$3), "x", "")

Not sure if you fully understand the logic of the formula? More information can be found atExcel SE with multiple AND/OR conditions.

This is how you use the SE and AND functions together in Excel. Thanks for reading and see you next week!

practice notebook

SE AND Excel – examples of formulas(.xlsx file)

You might also be interested in

• IF OR in Excel with Formula Examples
• Excel nested IF formula with multiple conditions
• Excel function IF ERROR
• Excel: if cell contains
• If cell contains, count, add, highlight, copy, or delete