How to Use AND Function in Excel With Example (5 Examples)

The AND function is one of the commonly used logical functions in the arena covered by the logical function family in Microsoft Excel. Briefly, using the AND function you can check if two or more conditions are met or not. You might believe that the usage of the AND function is more rudimentary. However, the uses of the AND function are extended when it is nested either within itself or with other logical functions such as OR, IF, and other functions. In this article, we will learn different ways of how to use the AND function in Excel with example.

Syntax of the AND Function

=AND(logic1, [logic2], ...)

Arguments of the AND Function

logic1 – Conditional or logical value you want to check by AND function.

logic2 – Optional condition or logical value you want to evaluate by AND function.

Return Value of the AND Function

• In terms of syntax, the AND function only returns in boolean TRUE when all of the inputs’ requirements are satisfied. If not, the AND function returns FALSE.

• The AND function counts Zero values as FALSE and Non-Zero values as TRUE if only numbers are evaluated.

• The AND function returns #VALUE! error if the argument refers to empty cells, none of the argument refers to a logical value, or the argument is passed as text.

Example #1: Test Multiple Conditions With AND Function

We now want to determine if the revenue and quantity of sales are larger than 50 and 500 respectively. Let’s follow some steps to solve the problem by the AND function with an example in Excel.

Step 1:

• Input the following formula in the output cell H3.

=AND(D3>50,F3>500)

Step 2: 

• Now, press ENTER to see the return value of AND function for the Sales representative named Adam.

Step 3: 

• Use the AutoFill tool to copy the formula in the rest cells of column H.

Explanation: 

• We already know the AND function returns TRUE or FALSE. In the example above, the return value for Adam is FALSE because the sales quantity for Adam is 12, which is less than 50. 
• Though revenue is larger than 500. But the AND function returns TRUE only when all conditions are complied with.

Example #2: Nest AND Function for Multiple Times 

Here, we want to use the nested AND function to evaluate some logic. Suppose, We want to know for which sales representative quantity is less than 15, the price is less than 10 and the revenue is greater than 500.

Step 1: 

• Simply, write the formula in the output cell H3 as shown below.

=AND(D3<15,AND(E3<10,F3>500))

Step 2: 

• Press Enter and Autofill the rest of the cells of column H.

Explanation: 

• AND(E3<10,F3>500) returns true only if E3 is less than 10 and F3 is greater than 500. For Adam which returns in TRUE.
• AND(D3<15,AND(E3<10,F3>500)) returns TRUE when D3 is less than 15 and the later nested AND function returns TRUE.
• Here, only Adam complies with the condition provided in the argument of the AND function. None of the other Sales representatives complies with all of the three conditions.

Example #3: Nest IF and AND Functions for Multiple Logical Criteria

In this example, we will apply the AND function combined with the IF function. To understand properly, you should know the following Syntax of the IF function.

Syntax

=IF(logic,[return1_if_true],[return2_if_false])

Arguments

logic: logical argument you want to check by the IF function.

return1_if_true: Value to show as a return if logic will comply.

return2_if_false: Value to show as a return if logic will not comply.

Let’s jump to an example. Suppose, you want to identify it as Good for sales representatives who have revenue and quantity of at least 500 and 50, respectively.

Step 1: 

• Insert the following formula into output cell H3.

=IF(AND(D3>50,F3>500), "Good", "Bad")

Step 2: 

• Press Enter and then AutoFill to see the status for all sales representatives.

Explanation:

• =IF(AND(D3>50,F3>500), “Good”, “Bad”) returns Good only if AND(D3>50,F3>500) returns in TRUE. Otherwise, the IF function will return in Bad.
• For Adam, though revenue is greater than 500, the quantity is less than 50. 
• So, AND(D3>50,F3>500) returns in FALSE. That’s why the IF function returns in Bad.

Example #4: Nest OR and AND Functions for Multiple Conditions

Before jumping to the example we should know the syntax of the OR Function.

Syntax

=OR(logic1, logic2, logic3,........)

Arguments:

logic1,  logic2,  logic3: all ere conditions or logical values to be tested.

Return Value

if any of the conditions are met, returns in TRUE; if none of the conditions are met, returns in FALSE.

Suppose, in addition to the previous example, you want to categorize the sales representatives who live in Texas or whose revenue is greater than 800.

Step 1: 

• Apply the following formula in the output cell H3.

=OR(C3="Texas",AND(F3>800))

Step 2: 

• To see the output, press ENTER and then just AutoFill the rest of column H.

Explanation:

• AND(F3>800) verifies whether the revenue is greater than 800 or not.
• OR(C3=”Texas”,AND(F3>800)) pick TRUE as a return if any one condition of two is satisfied.
• Here, Adam lives in Texas and the revenue is greater than 800 so both conditions are satisfied.
• But for Tom none of the conditions are satisfied, so OR Function returns FALSE.

Example #5: IF, OR, and AND Functions Combined Approach

Suppose, you want to give an increment to sales representatives who live in Texas or revenue is greater than 800 and Quantity is greater than 70.

Just follow the steps.

Step 1: 

• Input the following formula into the output cell H3.

=IF(OR(C3="Texas",AND(F3>800)),"✔","✗")

Step 2: 

• Hit the Enter key & then, AutoFill the rest part of column H.

Explanation:

• AND(F3>800) returns in TRUE when revenue is greater than 800.
• OR(C3=”Texas”,AND(F3>800)) returns in TRUE when Location is Texas or AND(F3>800) returns in TRUE.
• IF(OR(C3=”Texas”,AND(F3>800)),”✔”,”✗”) returns increment status (Tick mark “✔” ) when Nested OR function returns in TRUE. Otherwise, the IF function will return the“✗” mark.

Things to Keep in Mind

• AND Function is not Case Sensitive and does not support wildcards character. 
• AND Function can take 255 arguments in a single operation. 
• By AND function you can check Text, Date, and numerical values.

Frequently Asked Questions?

How to combine and/or in Excel?

Yes, you can combine AND, OF each other. Please see the following formula for your assistance:

=OR(C3=”Boston”,AND(F3>800))    //returns TRUE if any one of two conditions  (cell C3 is equal to “Boston”  or Nested AND Function) returns TRUE

Conclusion

In this article, we demonstrated the pros and cons, and the application of the AND function in Excel with example. We hope you really benefited from this article. It’s our pleasure to know if you have more queries about AND Function. Please comment below.