Blog Archive

EXCEL: Using AND Function



AND Function - Check if All Arguments are True

The AND function in Excel checks if every argument you put in it evaluates to TRUE.  If everything evaluates to TRUE, then the function will return TRUE, otherwise, even if only one argument is FALSE, the function will return the value FALSE.
This is the sister function to the OR function.
This is a very useful function and it is often used in conjunction with an IF statement in order to make more powerful logical tests in Excel.
Logical test: a logical test is any time you want to see if something is greater than, less than, equal to, or not equal to another thing.  Think of logical tests as anything that uses these operators: =, <, >, <>, >=, <=

Syntax


=AND(logical1, [logical2], etc.)
Argument Description
Logical1 This is the first argument or condition that you want to check to see if it evaluates to TRUE or not.  You do not need more than this 1 argument in the function.
[Logical2] etc. This argument is optional.  This would be another condition that you wanted to check to see if it evaluated to TRUE or FALSE.
You can have up to 255 conditions in this function if you are in Excel 2007 or later.
[] means it is an optional argument.
Each argument must evaluate to either TRUE or FALSE in order for this function to work.

Basic Example - Testing Numbers

Here, I want to show you how the function works by testing out numbers and making simple tests so you can better understand how this works.

The values in column A are all displayed in column B.
You can see the logical operators that you can use and that you can have multiple logical tests or arguments or just 1.

AND Function with Text

Here, we will compare text as well as numbers.  In this example, I will use cell references in the AND function instead of hard coding the values into the function.

You can see that checking text works just like checking numbers and you can also see that this check is case insensitive, which means that it does not matter if text is upper case or lower case or mixed case, it can still match.

AND Function Inside an IF Statement

Now, let's perform a check and output something other than TRUE or FALSE.  For this, we can use the IF statement.


=IF(AND(A1>B1,A2="yes"),"Proceed","Stop")
In cell C4 you can see the formula that is in cell C3.  We use the AND function to tell us if cell A1 is greater than cell B1 AND cell A2 equals "yes".  If both logical tests evaluate to true, we want to output Proceed and if any one of the logical test evaluates to false, we want to output Stop.
This is where the AND function really comes in handy and allows us to make much more useful formulas in Excel.

Notes

The AND function is quite simple all by itself but it is vital to creating more complex formulas in Excel.  Learn how to use this function well, especially in conjunction with the IF statement.
Make sure to download the workbook that accompanies this tutorial so you can see the examples in the worksheet.


Example of NOT function below: