What is Excel AND function?

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 function 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

=AND (condition1, [condition2], ...)

Where the conditions can be like this:

=AND(B2>A1)

OR

=AND(B3<A3, B4<100, C4 = 50)

The first AND function should return TRUE if B2 value is less than A1 cell value.

The second AND function will return TRUE only if the B3 is less than A3, B4 value is less than 100 and C4 cell value is equal to 50. If any of the condition is FALSE then AND function will return FALSE.

The section below shows using a few examples of 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:

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.

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:

Notice that, multiple conditions are separated by a comma. If any of the condition is false, the AND returned FALSE, as in the case of the first formula.

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 till D5 cell with respective B cells values and you can see the output.

You may learn more about the AND/IF in Excel here.

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 C3 cell is:

=AND(A3<30,B3>=10)

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.