Excel AND Function: How to Test Multiple Conditions

If you’ve ever found yourself second-guessing your Excel formulas, you’re not alone! Many business professionals I work with want to ensure their spreadsheets are doing exactly what they need—especially when it comes to checking if multiple conditions are true simultaneously.

That’s where the Excel AND function comes in. It’s a simple tool, but it can make a huge difference in how confidently you analyse your data and make decisions.

Consider those instances when you need to verify multiple conditions before taking action. Perhaps you want to confirm whether a sales figure meets your target and whether the offered discount is within company policy. Or maybe you’re tracking project deadlines and want to highlight only those tasks that are both approved and overdue.

Without the right formula, it’s easy to miss something important or spend ages double-checking your work.

In this guide, I’ll show you how the AND function can take the stress out of these situations.

You’ll learn exactly what the function does, how to use it in real business scenarios, and how to combine it with other Excel tools to save time and reduce errors.

I’ll also walk you through common mistakes and how to fix them, so you can use AND with confidence every day.

Excel Practice Files

What Does AND Do?

The AND function returns:

• TRUE if all conditions are met.
• FALSE if any condition fails.

Syntax

=AND(logical1, [logical2], …)

logical1, logical2 are conditions you want to test (e.g. A2=<50, A2=“Approved”).

Example 1: Check Two Conditions

Suppose you’re reviewing your monthly sales data and want to identify which products qualify for a special incentive quickly. The criteria are straightforward: sales must exceed $150,000, and the discount offered must be less than 25%. Instead of checking each row by hand, you can use the AND function to do the work for you.

By entering =AND(A1>150, B1<25), Excel will return TRUE only for those products that meet both requirements. This means you can instantly filter or highlight qualifying products, saving you time and reducing the risk of missing something important.

Using the AND function in this way helps you work smarter, not harder, giving you confidence that your reporting is both fast and accurate.

Formula: =AND(A1>150, B1<25)

Explanation: Returns TRUE if A1 > 150 and B1 < 25.

Business Scenario: Check if a product’s sales exceed $150k and the discount is under 25%.

Skill Level: Intermediate

Example 2: Combine AND with IF Function

Sometimes you want your spreadsheet to do more than say TRUE or FALSE—you want it to give a clear result, like “Bonus” or “No Bonus.” This is where combining the AND function with the IF function is so powerful.

For example, imagine you’re calculating staff bonuses. You only want to award a bonus if revenue is at least $200,000 and the margin is 25% or higher. By using the formula =IF(AND(A2>=200, B2>=0.25), “Bonus”, “No Bonus”), Excel will automatically check both conditions and return the correct result for each row.

This approach makes your bonus calculations quick, consistent, and easy to update—no more manual checking or second-guessing your logic.

Formula: =IF(AND(A2>=200, B2>=0.25), “Bonus”, “No Bonus”)

Explanation: If revenue is at least $200k and margin is 25% or higher, return “Bonus”; otherwise, “No Bonus”.

Business Scenario: Ideal for calculating staff bonuses based on meeting both revenue and margin targets.

Skill Level: Intermediate

Example 3: AND Function in Conditional Formatting

Conditional Formatting in Excel is a fantastic way to make important information stand out. By using the AND function within Conditional Formatting, you can highlight cells or rows only when multiple conditions are met. For instance, you might want to flag projects that are both “Approved” and overdue.

With a formula like =AND(A2=”Approved”, B2<TODAY()), Excel will automatically highlight only those projects that meet both criteria. This makes it much easier to spot issues at a glance, helping you stay on top of deadlines and priorities without having to manually sift through your data.

Formula: =AND(A2=”Approved”, B2<TODAY())

Explanation: Checks if the status is “Approved” and the date is before today.

Business Scenario: Highlight overdue approved projects in a project tracker.

Skill Level: Intermediate

Example 4: AND with OR Function

Sometimes your business rules are a bit more complex—you need to check that one condition is true, but you also want to allow for a couple of different options in another field. By combining AND with OR, you can create flexible logic that fits real-world scenarios.

For example, you might want to filter tasks that are confirmed (A2 = “Yes”) and have either high or medium priority (B2 = “High” or “Medium”). The formula =AND(A2=”Yes”, OR(B2=”High”, B2=”Medium”)) does exactly that, making it easy to focus on the tasks that matter most.

Formula: =AND(A2=”Yes”, OR(B2=”High”, B2=”Medium”))

Explanation: TRUE if A2 is “Yes” and H2 is either “High” or “Medium”.

Business Scenario: Filter tasks that are confirmed and have high or medium priority.

Skill Level: Intermediate

Troubleshooting Excel AND Function

When working with the Excel AND function, you may occasionally encounter unexpected results or error messages. Understanding the most common issues and how to resolve them will help you use the AND function with confidence and accuracy. Let’s look at some typical problems and their solutions.

#VALUE! Error

This error usually occurs when one of the arguments in your AND formula isn’t a valid logical expression.

Example:
=AND(A2>100, B2<“Discount”)

This will fail because “Discount” is text, not a number. Therefore, the less-than < operator is creating an invalid expression.

Fix: Ensure all conditions compare numbers or valid text values correctly. For text validation, use the equal to = or not equal to <> operators.

Incorrect Logic

The AND function only returns TRUE when all conditions are met. If you need a formula that returns TRUE when any condition is met, use the OR function instead.

Example:
=AND(Sales>100, Discount<50) will be TRUE if both conditions are met.

Fix: If you want TRUE when either condition is met, use the OR function, =OR(Sales>100, Discount<50).

Missing Parentheses

Every opening bracket must have a closing bracket. Missing parentheses will cause Excel to show an error or misinterpret your formula.

Example:
=AND(A2=”Approved”, B2<TODAY() has a missing closing parenthesis for the AND function.

Fix:
=AND(A2=”Approved”, B2<TODAY())

FAQs

Q. Can AND test more than two conditions?
A. Yes, up to 255.

Q. Does AND work alone?
A. Yes, but often combined with IF.

Related Functions

• OR  – Combine multiple conditions (any can be TRUE)
• NOT – Negate a condition
• IF – Single condition checks
• IFS  – Test multiple conditions in order, returning the first TRUE result

Excel Practice File Download

Conclusion: Excel OR Function

The AND function is a fantastic way to check multiple conditions in Excel with confidence.

Once you get the hang of it, you’ll save time, reduce errors, and feel more in control of your data. Give it a try in your next spreadsheet—you’ll be amazed at how much easier your work becomes!