- The AND is a logical function in Excel that determines if all the conditions in a test are TRUE.
- The other logical function examples are OR, NOT, IF, SWITCH, etc.
- You may use the AND function by itself or in conjunction with other functions like IF.
- I have written a comprehensive tutorial about using AND with IF function: Excel AND with IF
The syntax for using AND function itself
Where the conditions can be like this:
OR
The first AND function should return TRUE if the B2 value is less than A1 cell value.
The second AND function will return TRUE only if the B3 is less than A3, the B4 value is less than 100 and C4 cell value is equal to 50. If any of the conditions is FALSE then AND function will return FALSE.
The section below shows using a few examples of the AND function by itself along with using AND with the IF function.
An example of AND with the single condition
In this example, I used plain values for the AND function in various cells. I gave only one condition and see the formulas and results:
The following formulas are applied from B2 to B5 cells:
B2: =AND(A2<10) B3: =AND(A3>=20) B4: =AND(A4=30) B5: =AND(A5>30)
You can see the result returned by AND function in the above screenshot.
Using multiple conditions in the AND function example
Now see various possibilities as I used multiple conditions in the AND function.
The following formulas are used for the C2 to C5 cells:
C2: =AND(A2<10,B2<=60) C3: =AND(A3>=20,B3>=60) C4: =AND(A4=30,B4=80) C5: =AND(A5>30,B5<=90)
You can see the results below:
In the case of the first formula, the first condition is False while the second condition is True.
The example of using AND with IF function
As mentioned earlier, you may also use the AND with the IF function.
In that case, the AND function acts as the logical test for the IF function.
An example of using the AND/IF function is shown below where we will check the sale amount in the C column and test if the salesperson is qualified for a bonus or not.
The result is displayed in the D cell:
The sample formula for D2 cell with AND/IF:
=IF(AND(B2>=50,C2>10000),"Qualified","Not Qualified")
The same is applied to D5 cells with respective B cells values and you can see the output.
What if a text value is provided in AND function?
If a referred cell contains the text value, the AND function ignores it. See an example below where I used “test” as the value in the B3 cell.
The formula for the C3 cell:
See the result yourself below:
You can see, as the first condition is TRUE i.e. A3 value is less than 30, the second condition is ignored. The final result is still “TRUE” returned by the AND function.