If you want to check whether several conditions are all true at the same time, the AND function is what you’re looking for.
It returns TRUE only when every condition you give it is met, and FALSE as soon as even one of them fails.
One thing to know up front: AND always returns a single TRUE or FALSE. So it does not spill down a column the way many functions do in Excel 365.
In this article, I’ll walk you through the AND function with practical examples, from simple checks to using it inside IF and dynamic array formulas.
AND Function Syntax
Here is the syntax of the AND function:
=AND(logical1, [logical2], ...)
- logical1 – the first condition you want to test. This can be a comparison like B2>100, a cell that already holds TRUE or FALSE, or a range of logical values. Required.
- logical2, … – optional extra conditions to test. You can add up to 255 conditions in total.
The result is always a single value: TRUE if every condition is met, and FALSE if even one of them is not.
Pro Tip: If you hand AND a range that contains text or empty cells, those are ignored. But if the range has no logical values at all, AND returns a #VALUE! error.
When to Use the AND Function
Use the AND function when you need to:
- Check that several conditions are all true before you take an action
- Test whether a number falls between a lower and an upper limit
- Combine multiple tests inside an IF function to return your own custom results
- Build conditional formatting rules that only trigger when every condition is met
Let me show you a few practical examples of how this works.
Example 1: Check If Multiple Conditions Are All True
Let’s start with a simple example.
Below is an orders table with the order ID in column A, the order value in column B, whether the item is in stock in column C, and the shipping destination in column D.
I want to flag which orders qualify for free shipping, which needs an order value of at least 50, the item in stock, and a domestic destination.
Here is the formula:
=AND(B2>=50, C2="Yes", D2="Domestic")
AND checks all three conditions for that row. The order value has to be 50 or more, the in-stock cell has to say Yes, and the destination has to be Domestic.
Only when all three are true does the formula return TRUE. If even one fails, you get FALSE. Copy it down the column to check every order.
Notice the text conditions “Yes” and “Domestic” are wrapped in double quotes, while the number comparison B2>=50 is not.
Example 2: Check If a Number Is Between Two Values
Here’s a really common use for AND: testing whether a value sits inside a range.
Below I have temperature readings from a cold-storage unit, with the reading time in column A and the temperature in Celsius in column B. The safe range for this unit is between 2 and 8 degrees.
I want to check whether each reading is inside that safe band.
Here is the formula:
=AND(B2>=2, B2<=8)
This is where AND really shines. A single cell can’t be greater than 2 and less than 8 in one comparison, so you need two separate tests joined together.
The first condition checks the reading is 2 or higher. The second checks it’s 8 or lower. A reading of 5.4 passes both and returns TRUE, while 9.2 fails the upper limit and returns FALSE.
The same two-test pattern works with dates too, which is handy when you need to check if a date is between two dates.
Example 3: Use AND Inside an IF Function
The AND function gets a lot more useful once you nest it inside IF. On its own AND only gives you TRUE or FALSE, but IF lets you return whatever text or number you want instead.
Below is a rental application table with the applicant in column A, their credit score in column B, annual income in column C, and whether they have a prior eviction in column D.
I want to mark an application as “Approve” only when the credit score is at least 650, income is at least 40000, and there is no prior eviction.
Here is the formula:
=IF(AND(B2>=650, C2>=40000, D2="No"), "Approve", "Manual Review")
Here AND does the checking and IF does the labeling.
AND tests all three conditions and hands a single TRUE or FALSE back to IF. When it’s TRUE, IF returns “Approve”. When it’s FALSE, IF returns “Manual Review” instead of a bare FALSE.
An applicant with a 712 score, 58000 income, and no eviction passes all three tests, so this returns “Approve”.
Example 4: Combine AND with OR for Mixed Logic
Sometimes your rules aren’t all “must be true”. You need some conditions that are strict and one that can be satisfied a couple of different ways. That’s when you nest OR inside AND.
Below is a flight upgrade list with the passenger in column A, their loyalty tier in column B, seats available in column C, and their fare class in column D.
I want to mark a passenger “Eligible” when they are Gold or Platinum tier, there is at least one seat available, and their fare class is not Basic.
Here is the formula:
=IF(AND(OR(B2="Gold", B2="Platinum"), C2>0, D2<>"Basic"), "Eligible", "Not Eligible")
The OR part handles the tier. It returns TRUE if the passenger is Gold or Platinum, so either one counts.
That result then joins the other two AND conditions: a seat is free, and the fare class is anything other than Basic. All three parts of the AND have to be true for the passenger to come back “Eligible”.
A Gold passenger with 3 seats free on an Economy fare passes every check, so the formula returns “Eligible”.
Example 5: Apply AND Logic in a Dynamic Array Filter
Let’s finish with something modern. If you’re on Excel 365 or 2021, you’ll often want row-by-row AND logic across a whole range, usually to filter a list down to the rows that meet every condition.
There’s a catch here worth knowing. You can’t just drop the AND function inside FILTER. Writing AND(B2:B13>=20, ...) collapses the entire range into one TRUE or FALSE, so FILTER can’t tell the rows apart.
The fix is to multiply the conditions together with *. Multiplication works element by element, so it acts as an AND on each row without collapsing anything.
Below is a product list with the product name in column A, price in column B, and whether it’s in stock in column C.
I want to pull out only the products priced between 20 and 60 that are also in stock.
Here is the formula:
=FILTER(A2:C13, (B2:B13>=20)*(B2:B13<=60)*(C2:C13="Yes"))
Each condition in parentheses produces a TRUE/FALSE array, one value per row.
Multiplying them turns TRUE into 1 and FALSE into 0, so a row only survives when all three come out as 1. FILTER keeps those rows and spills the matching products into the cells below.
Pro Tip: Use * between conditions for AND logic, and + for OR logic. The FILTER function and spill ranges need Microsoft 365 or Excel 2021, so this won’t work in Excel 2019 or earlier.
Tips & Common Mistakes
- AND returns one value, not one per row. Feeding it a whole range like =AND(B2:B10) collapses to a single TRUE or FALSE for the entire range, not a separate answer for each row.
- For row-by-row AND logic, multiply the conditions. Inside a dynamic array formula, join your conditions with * instead of the AND function, as shown in Example 5.
- AND ignores text and blank cells in a range. Those are skipped, but if a range has no logical values at all, AND returns a #VALUE! error.
- You can stack up to 255 conditions. That’s far more than you’ll usually need, and every one of them has to be true for the result to be TRUE.
- Nest AND inside IF for custom output. On its own AND only gives TRUE or FALSE, so wrap it in IF when you want your own text or numbers.
- Use OR when any one condition can be true. AND needs all conditions met. If you only need one of several to pass, reach for the OR function instead, and use NOT to flip a result.
Wrapping Up
The AND function is a small but handy tool once it clicks. We covered checking several conditions at once, testing whether a number sits between two values, and using AND inside IF, OR, and dynamic array formulas.
I hope you found this tutorial helpful.
Other Excel Articles You May Also Like:
