AND Function in Excel with Example

AND ()

This function tests two or more conditions to see if all of them are true.

It can be used to test that a series of numbers meet certain conditions. It can be used to test that a number or a date falls between an upper and lower limit. Normally the AND() function would be used in conjunction with a function such as =IF().

Best view in desktop mode.

	A	B	C	D	E	F
1	Order No.	Cost	Payment Type	Handling Charge
2	AB001	1000	Cheque	FALSE	=AND(B2>=2000,C2="Cheque")
3	AB002	1000	Visa	FALSE	=AND(B3>=2000,C3="Cheque")
4	AB003	2000	Cheque	TRUE	=AND(B4>=2000,C4="Cheque")
5	AB004	5000	Delta	FALSE	=AND(B5>=2000,C5="Cheque")
6

Syntax

=AND(Test1,Test2)

Note that there can be up to 30 possible tests.

It can be used on single and multiple columns, rows.

Formatting

When used by itself it will show TRUE or FALSE.

Example 1

The following table shows a list of orders taken by a company.

A handling charge of Extra 5% is made on all orders paid by cheque with order value 2000 and more.

The =AND() function has been used to determine whether the charge needs to be applied.

	A	B	C	D	E	F
1	Order No.	Cost	Payment Type	Handling Charge
2	AB001	1000	Cheque	-	=IF(AND(B2>=2000,C2="Cheque"),"Extra 5% Charge","-")
3	AB002	1000	Visa	-	=IF(AND(B3>=2000,C3="Cheque"),"Extra 5% Charge","-")
4	AB003	2000	Cheque	Extra 5% Charge	=IF(AND(B4>=2000,C4="Cheque"),"Extra 5% Charge","-")
5	AB004	5000	Delta	-	=IF(AND(B5>=2000,C5="Cheque"),"Extra 5% Charge","-")
6

Example 2

In a sports academy, only blue color balls are allowed. And show the allowed items as true.

	A	B	C	D	E
1	Color	Item	Allowed
2	Red	Ball	FALSE	=AND(A2="Blue",B2="Ball")
3	Green	Hockey	FALSE	=AND(A3="Blue",B3="Ball")
4	Red	Shoes	FALSE	=AND(A4="Blue",B4="Ball")
5	Blue	Ball	TRUE	=AND(A5="Blue",B5="Ball")
6	White	Racket	FALSE	=AND(A6="Blue",B6="Ball")
7

OR in Excel

<<  Previous

Next  >>

NOT in Excel