The excel AND function is one of the highly used logical functions in excel. The term ‘logical function’ means all those functions which check some condition and criteria in excel, and based on the condition fulfillment, it returns the result.
In this tutorial we would deep dive into following area of excel AND formula.
- What is the use of AND function in excel
- Formula syntax and arguments
- Example of AND function in excel
- Some important points
So, let us begin 😎
Purpose and Objective of AND Function in Excel
The =AND() formula in excel is a logical excel function that checks multiple conditions or criteria at the same time and returns TRUE or FALSE based on the below logic:
- If ALL the specified conditions fulfil, then the formula would result in ‘TRUE‘.
- On the other hand, if any one or more conditions does not fulfil, then the formula would result in ‘FALSE‘.
See the table below for better understanding:
Syntax and Input Arguments
This formula has following input arguments:
- logical1 – In this argument, specify the first condition that you want to check if it fulfils or not.
- [logical2] – The second logical argument is optional. Enter the condition in this argument, if you want to check for the second condition as well.
If you want to check for more than two conditions, then add more arguments inside the parenthesis, each separated by a comma.
=AND(logical1, [logical2], [logical3], [logical4], …)
In order to effectively use the AND formula or other logical excel functions, one must know the logical operators that excel uses.
- <> : This is a ‘Not Equal To‘ sign. It checks if the left side of the input argument does not equal the right-hand side value.
- = : This is an ‘Equal To‘ sign. It checks if the left side of the input argument equals the right-hand side value
- > : This is a ‘Greater Than‘ sign. It checks if the left side of the input argument is greater than the right-hand side value.
- < : This is a ‘Less Than‘ sign. It checks if the left side of the input argument is less than the right-hand side value.
- >= : This is a ‘Greater Than or Equal To‘ sign. It checks if the left side of the input argument is greater than or equal to the right-hand side value.
- <= : This is a ‘Less Than or Equal To‘ sign. It checks if the left side of the input argument is less than or equal to the right-hand side value.
Simple Example of AND Excel Function
Let us suppose, that a student needs to score at least 45 marks in each subject to pass an examination.
Following are the marks of four students in four subjects.
Since we want to check if the marks are at least 45, we shall use the logical operator as “Greater than or Equal To” (>=).
The AND excel formula would be very helpful in determining whether the student has passed or failed. See the below image:
In the above example, since the student ID 0001 did not get minimum required marks in Maths and Arts, therefore, the result of the AND formula is FALSE. Similarly, student ID 0003 did not earn minimum marks in Science, hence AND formula returns FALSE.
Do Not Miss These Important Points
- A single AND function can accept a maximum of 255 logical conditions (i.e. logical1, logical2, …… logical255).
- The result of the AND formula is either TRUE or FALSE. TRUE if all conditions match, and FALSE if any one of the conditions does not match.
- If a cell is empty or blank, the AND function would ignore it for condition check. Similarly, the text cell is also ignored for the purpose of condition check.
- Generally, the AND formula and other such logical functions are not used singly. They are embedded in other function such as IF formula, to get more meaningful result.