How to Use Excel AND Function

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.

Introduction

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.

1. Check if column B contains "Paid" AND column D contains "Shipped".
2. IF column B contains "Paid" AND column D contains "Shipped", show "Completed" in column F

How the AND Function Works

There is one required argument for the AND function, and up to 255 arguments are allowed. Here is the syntax:

AND(logical1, [logical2], ...)

1. logical1 -- The first test, such as the value in a cell.
2. [logical2] -- (optional) Another test to run. Up to 255 tests allowed in the AND function

AND Traps

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

Example 1: Check Values in Two Columns

In this example, the AND function will do two tests:

1. Does the Payment column (B) contain "Paid"
2. Does the Status column (D) contain "Shipped"

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.

Example 2: Use AND With IF Function

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.

1. IF both tests are TRUE (column B contains "Paid" AND column D contains "Shipped"), the result will be "Completed" in column F.
2. IF either test is FALSE (column B contains "Paid" AND column D contains "Shipped"), the result will be "Pending" 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".

Example 3: Check a Date Range

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)

1. If both tests are TRUE (Order date is greater than or equal to the start date, AND less than or equal to the end date), the result is TRUE
2. IF either test is FALSE (Order date is greater than or equal to the start date, AND less than or equal to the end date), the result is FALSE.

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 File

Download the sample AND workbook

More Tutorials

Functions List

30 Functions in 30 Days

COUNT / COUNTIF

IFERROR

SUM / SUMIF

VLOOKUP

MAX IF

Get Excel News