Learn how to write AND function in Excel like a pro with Ted Jordan: discover the AND function syntax, how to use it with multiple criteria or text and when to use it.
Watch our short video and examples to understand how AND works in various situations.
Ready to increase your Excel knowledge?
Let’s go!
What is AND in Excel?
The AND function determines if all conditions included in its syntax are met: if so, TRUE will appear as the result of the AND formula. If not all conditions are met, FALSE will be displayed.
Function syntax: how to write AND function
The AND function syntax is =AND(logical1, [logical2], …) with logical1 being the only required argument.
logical1 — this argument represents the 1st condition you want to test with the AND formula.
logical2… — additional conditions to test with the AND formula.
Notes
– You can add up to 255 conditions in the AND function in Excel.
– Empty cells will be ignored by the system.
– For conditions with text elements, use quotation marks “” in logical values or the system will skip these cells.
When to use AND in MS Excel
We recommend to use the AND function when you want to test multiple conditions at the same time.
To display specific values for TRUE or FALSE result, you can combine AND with IF for example.
Click here to learn how to combine AND and IF functions.
How to use the AND function
Use the AND formula in Excel when you want to check if some conditions are all met. You can of course use AND for 1 condition if you wish to, or use this handy function with IF to display specific results.
Click here to know more about the IF function in Excel.
AND function with 1 criteria but multiple cells: how to do it?
You can test one criteria (condition) in Excel for multiple cells easily with the AND function.
For example, =AND(B22:E22>0) will check if cells B22, C22, D22 and E22 contain numbers higher than 0. If so, TRUE will be the result and if not, FALSE will appear.
In this example, AND helps us check if our customers ordered with us each quarter: if they did, we will send them a promo code to thank them.
Learn how we highlighted TRUE results automatically with Conditional Formatting here.
AND function with 3 criteria or more
You’ll see it’s easy to use AND function in Excel for 3 or more conditions.
First, click the cell where you want the result to appear.
Then, enter =AND(condition1, condition2, condition3) and press Enter.
In our example, we want to check if some clients increased the number of orders each quarter. If so, we will send them a promo code to thank them!
condition1 is C>B
condition2 is D>C
condition3 is E>D
If all these conditions are met, the result will be TRUE. If not, FALSE will appear.
In this example, Tanguy and Andy ordered more with us quarter after quarter.
Thanks to Conditional Formatting, it’s easy to see TRUE results!
AND function with text: how to write it
Use AND with text elements in Excel by writing text within quotation marks “”.
For example, =AND(B22:E22>0,A22=”Ted”) will be TRUE if cells B22 to E22 contain numbers higher than 0 and cell A22 contains Ted or ted.
To help you understand, if all cells are ticked with a green sign, the result is TRUE. If not, the result is FALSE.
Now, you know how to write AND function in Excel for one or multiple conditions, for one or multiple cells; you also learned how to write AND function with text for the system to check conditions.
Don’t waste your Excel knowledge: get certified!
Join our MS Excel Online Course: learn at your own pace, receive support from Excel experts by email, receive a free Spreadsheet Cheat Sheet and much more!
