AND Function in Excel

AND Function in Excel (Table of Contents)

• AND Function in Excel
• AND Formula in Excel
• How to use AND Function in Excel?

AND Function in Excel

AND function is an inbuilt function in Excel that is categorized under Logical Function, the logical function operates under a logical test

Helps to evaluate or test multiple conditions

It Returns the value TRUE if all of the arguments evaluate to TRUE, if any one of the tests comes out as false, AND delivers a result as FALSE

It is the most commonly used logical function alone or in combination with other operators like

• “IF”
• “OR”
• “NOT”
• < (less than)
• > (greater than)
• = (equal to)
• <= (less than or equal to)
• >= (greater than or equal to)
• <> (not equal to)

It is most commonly used in conditional formatting

AND function in Excel is used to test multiple conditions, up to 255 logical conditions can be used at the same time (Note: In Excel 2003 version, the function can only handle up to 30 arguments)

AND function can operate on text, numbers, or cell references

AND function needs at least two arguments to return a value. Arguments are separated by comma, At the end, it provides either a ‘true’ or ‘false’ output based on the arguments inside the AND function.

AND Formula in Excel

Below is the AND Formula in Excel.

logical1: logical value to evaluate, it’s a first condition to evaluate

logical2: logical value to evaluate, it’s a second condition to evaluate

How to Use the AND Function in Excel?

This AND function in excel is very simple easy to use. Let us now see how to use AND function in Excel with the help of some examples.

AND in Excel – Example #1

If we enter =AND (5>2, 6=6) in an excel cell, the answer or formula result would be TRUE. It’s true because 5 is greater than 2 and 6 does equal 6.

AND in Excel – Example #2

If we enter =AND (5>2, 6=8) in an excel cell, the answer or formula result would be FALSE. It’s false because 5 is greater than 2, but 6 is not greater than 8

NOTE:

• In AND function formula, each term inside the parentheses is separated by a comma like other function in excel
• If an array or reference argument contains text or empty cells, those values are ignored.
• If the specified range contains no logical values AND returns the #VALUE! error value.

AND in Excel – Example #3

In this example, if a student passes all the subject, then he has passed the exam

=AND (B6=”PASS”, C6=”PASS”, D6=”PASS”, E6=”PASS”)

The text strings, stored in column B, C, D & E must be “PASS”. Each term inside the parentheses need to be separated by a comma like other function in excel, here it returns the value TRUE if all of the arguments evaluate to TRUE

i.e. if students has passed all the subjects, it returns the value TRUE,

otherwise, even if the students have failed in any of the one subjects, it returns the value FALSE

AND in Excel – Example #4

Using other operators (> (greater than) = (equal to) along with AND Function

AND function returns TRUE if first logical condition, Gender parameter I12 = MALE, and the second logical condition, age parameter should be equal to or more than 45 years i.e. H12>=45. else it returns FALSE. Here with reference to HARRY, it returns the value TRUE, 

In the case of other candidates, SUMITH, it returns the value FALSE.

AND in Excel – Example #5

Using AND with “IF” Function with other operators

Suppose, you have a table with the various subject score, need to find out the results of exam whether they have passed the exam or not

The first score, stored in column B with the first subject, must be equal to or greater than 40. The second score, listed in column C with the second subject, must be equal to or greater than 40. Similarly, the third & fourth score, with third & fourth subject stored in column D & E, must be equal to or greater than 40. If all the above four conditions are met, a student passes the exam.

The easiest way to make a proper IF/AND formula is to initially write down the condition first, and later add it in the logical test argument of your IF function:

Condition: AND(B18>=40,C18>=40,D18>=40,E18>=40)

IF/AND formula: =IF(AND(B18>=40,C18>=40,D18>=40,E18>=40),”PASS”,”FAIL”)

AND in Excel – Example #6

Using AND function with “OR” FUNCTION

Using IF and AND together is actually quite simple, In the below-mentioned example, irrespective of brand, in the table array, need to find out the sales of red color shoes which are more than 50 numbers by using AND function with “OR” function.

The first column contains a number of shoes sold, the second column contains brand of shoes, the third column contains color of shoes, Here the condition is data in the I column should be equal to or more than 50 & simultaneously the color of the shoes should be red. If both the above conditions are met, it returns the value “TRUE” otherwise “FALSE”

You can download this AND function in Excel template here – AND Function Excel Template

Recommended Articles

This has been a guide to AND Function in Excel. Here we discuss the AND Formula in Excel and how to use AND in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

1. How to Use FV Function in Excel?
2. Excel LOOKUP Function
3. Top Microsoft Excel Shortcuts (Advance)
4. Excel Formulas and Functions