Use the AND function to combine 2 or more tests, such as column B contains "Paid" and column D contains "Shipped". The AND function will result in TRUE, if all the tests are true. Combine with other functions, such as IF function.
With the AND function, you can combine 2 or more tests, and see if all of them are True. If one or more of the tests are False, the AND function will return FALSE as its result. The AND function will result in TRUE, if all the tests are true.
You can combine AND with other functions, such as the IF function. Download the sample file to see how AND is used in the following examples. Step by step instructions are shown below.
There is one required argument for the AND function, and up to 255 arguments are allowed. Here is the syntax:
AND(logical1, [logical2], ...)
Here are things to watch for, when working with the AND function.
TRUE or FALSE -- Each test must result in a logical value, such as TRUE or FALSE, or must be an array or a references that contains logical values.
Empty Cells -- Arrays or references to text or empty cells are ignored
No Logical Values -- If there are no logical values in the range, the result is an #VALUE! error
In this example, the AND function will do two tests:
The completed formula is: =AND(B3="Paid",D3="Shipped")
In the screen shot below, cell B3 is "Paid", so the first test is TRUE, and cell D3 is "Back Order", so the second test is FALSE. Because one of the tests is FALSE, the result of the AND formula is FALSE.
In row 4, cell B4 is "Paid", so the first test is TRUE, and cell D4 is "Shipped", so the second test is TRUE. Because both tests are TRUE, the result of the AND formula is TRUE.
Instead of writing a long IF formula, to do multiple tests, use the AND function, nested in the IF function. In this example, IF column B contains "Paid" AND column D contains "Shipped", the result will be "Completed" in column F.
The completed formula is: =IF(AND(B4="Paid",D4="Shipped"),"Completed","Pending")
In the screen shot below, cell B3 is "Paid", so the first test is TRUE, and cell D3 is "Back Order", so the second test is FALSE. Because one of the tests is FALSE, the result of the formula is "Pending".
In row 4, cell B4 is "Paid", so the first test is TRUE, and cell D4 is "Shipped", so the second test is TRUE. Because both tests are TRUE, the result of the formula is "Completed".
In this example, the AND function does two tests on the same cell, to check if the Order date is between the start date (C2) and the end date (E2)
The completed formula is: =AND(B5>=$C$2,B5<=$E$2)
In the screen shot below, the order date in cell B4 is greater than or equal to the start date, so the first test is TRUE. Also, the order date in cell B4 is less than or equal to the end date, so the second test is TRUE. Because both of the tests are TRUE, the result of the formula is TRUE.
In the screen shot below, the order date in cell B5 is greater than or equal to the start date, so the first test is TRUE. However, the order date in cell B5 is NOT less than or equal to the end date, so the second test is FALSE. Because one of the tests is FALSE, the result of the formula is FALSE.
Download the sample AND workbook
Last updated: May 10, 2021 3:27 PM