The AND function checks if all provided conditions in the function are true, and returns a TRUE. If any of the conditions are false, a FALSE is returned.
Contents:
Syntax
= AND(logical1, [logical2], [logical3], ...)logical = the logical condition to be checked. Each logical expression can be a comparison of two values, or separate logical function
Explanation
The AND function is part of the "Logical" group of functions within Excel.
The AND function checks if all the conditions provided in the logical input arguments are true or not. It takes one or more logical expressions as arguments, and returns the logical value of TRUE if all the conditions are true, and FALSE if any of the conditions are false.
AND Function Logical Inputs
Comparison operators: You can use comparison operators like "=", ">", "<", ">=", "<=", and "<>" to compare two values and return a logical value of TRUE or FALSE. For example, A1>10 will return TRUE if the value in cell A1 is greater than 10, and FALSE if it is not.
Cell references: You can also use cell references to check if a cell contains a logical value (TRUE or FALSE). For example, the formula:
=AND(A1=TRUE, B1=FALSE) will check if the value in cell A1 is TRUE and the value in cell B1 is FALSE.
Logical functions: You can also use other logical functions like IF, NOT, and OR as inputs for the AND function. For example, the formula:
=AND(IF(A1>5, TRUE, FALSE), NOT(B1=TRUE)) will use the results of two other logical functions as inputs for the AND function.
You can also use combination of those, for example:
=AND(A1>5, OR(B1<10,C1="Yes"))Notes:
- A #VALUE error will be returned if no logical values are found
- The AND function does not support wildcards, and is not case sensitive
- Empty and blank cells are ignored
Examples
1. How to Return a TRUE or FALSE Based on Two Columns
A common use case for the AND function is returning a TRUE or FALSE based on the values of two (or more) columns.
In this example, we want to know if a number falls between two separate values in one column, and another column equals a specific text string in a another column.
Let's say you have a spreadsheet with two columns: "Expense Category" and "Expense Amount". You want to know which expenses for the "Travel" category are greater than $1,000 and less than $5,000.
First, you would create a column with a logical expression that checks if each expense is in the "Travel" category and if the expense amount is greater than $1000 and less than $5000. You can use the formula:
= AND(A2 = "Travel", B2>1000, B2<5000)
2. How to Combine the AND Function with the OR Function
In this example, we have a spreadsheet with employee information, including columns for "Employee Name", "Hire Date", "Job Title", and "Salary". We will use the AND function to find the number of employees who have been hired in the last 3 years and have the job title of "Manager" or "Director".
Because the AND function can also compare dates as well as values, the following formula can be placed in column F, and will check if each employee meets the two conditions (hired in the last 3 years and has a job title of "Manager" or "Director").
= AND(TODAY() - D1 <= 365*3, OR(E1="Manager", E1="Director"))In this formula, today's date must be within three years (365*3), and the job title has to either be a "Manager" or "Director". By using the OR function, our second logical input will be a TRUE if either of these titles is found.
3. How to Check Multiple Conditions at the Same Time in an IF Statement - Combining AND and IF Functions
Another common example would be using the AND function inside of an IF statement to checking multiple conditions.
Another example could be in a customer database, where you have columns for "Customer Number", "Customer Name", "City", "State", and "Status". You could use the AND function to find all customers who live in the state of "California" AND have a status of "Active", and return their customer number.
= IF(AND(D2="California", B2="Active"), A2,"")This formula will check if both conditions are met and return the corresponding customer number from column A, if not it will return a blank. This would let you quickly filter for the lines you're looking for, and give you a quick list of relevant customer numbers.
