The AND Function in excel is a logical function that is used to check if multiple conditions are true or false. It returns a Boolean TRUE if all the conditions turn out true and FALSE if one or more of the conditions are false. The AND function is often used with other logical functions such as the IF and IFS functions to test for complex conditions and return specified values if true or false.
In today’s post, we would take a look at the AND Function and just how versatile it is. We would kick off with the syntax and argument, and then proceed with various examples to illustrate (including a combination with the OR Function and IF Function).
Syntax & Argument
The syntax of the AND function is as follows:
=AND(logical1, [logical2], …)
There can be as many as 255 arguments when using this function. The arguments represent conditions which excel would test whether true or false. The Syntax above shows only 2 arguments, but the dot (….) indicates that additional arguments can be included.
- logical1: This is the first condition to test; it is mostly used with logical operators such as >, <, =, <=, >=, <>, but we could also have a formula that calculates into either TRUE or FALSE.
- logical2: Represents the second condition to test, and it could also be a test with logical operators or a formula that calculates into either TRUE of FALSE.
Let’s move on to some of the usage notes of this function in our next section.
Usage Notes
There are some things to note when using this function, let’s get right into it shall we.
- The maximum number of arguments you can have for each AND function is 255 arguments
- Arguments for the AND function can be any logical value including cell references or expressions that evaluate into a logical value.
- For an AND function to return TRUE, all arguments contained within must evaluate to true, if any of the arguments evaluates to False, then the AND function would return FALSE.
- The AND function can be nested with other functions such as the IF Function to test for more complex conditions.
AND Function Examples
Basic Example
Let’s assume that cell B3 contains the number 25, and we use the AND to test for the scenarios below:
=AND(B3>0,B3>10,B3<20) // Returns FALSE because the last condition (B3<20) is false
You can use the AND function to test cells that fall within a range. For example, we could test whether cell A3 is greater than 5 and less than 25 by using the formula below.
And OR Combination
Imagine a scenario where you have a set of conditions, one of those conditions is a must have, but you only need one from the remaining 3. In this scenario, we can’t use the AND function alone to test for these conditions because remember that all our criteria must be true for the AND function to return TRUE. Luckily however, we can combine the AND function with the OR function to achieve what we need.
In our example in this scenario, we have an individual considering 3 offers she received, and there are 4 factors she is considering in her evaluation of the offers:
The following are her preference for the factors being considered:
- Salary Offered: Greater than 50,000
- Airtime: At least 5000
- Commute time: Less than 30 minutes
- Higher Position: Yes
Of the four factors, the first one (salary offered) is the deal breaker, if the requirement of this is met along with one of the other three factors, then she can accept the offer. As stated in the introduction, this is impossible to do with the AND Function alone, but with a AND OR Combination, we can achieve this easily. Let’s see the formula to use below
AND IF Function Combination
Building on the previous example, let’s return results other than TRUE or FALSE. By combining our previous formula with the IF Function, we can return a custom result depending on whether the outcome is true or false. We want to return “Consider” if true and “Dont Consider” if False.
