Excel AND/OR Functions

last modified April 4, 2025

The AND and OR functions are logical functions in Excel that evaluate multiple conditions. AND returns TRUE only if all conditions are true, while OR returns TRUE if any condition is true. This tutorial provides a comprehensive guide to using these functions with detailed examples. You'll learn basic syntax, practical applications, and advanced techniques.

AND/OR Function Basics

The AND and OR functions evaluate logical conditions. They are often used with IF to create complex conditional formulas. The syntax is similar for both functions.

Function	Description	Syntax
AND	Returns TRUE if all arguments are TRUE	=AND(logical1, [logical2], ...)
OR	Returns TRUE if any argument is TRUE	=OR(logical1, [logical2], ...)

This table compares the essential characteristics of both functions. They can accept 1-255 logical conditions and return either TRUE or FALSE based on the evaluation.

Basic AND Example

This example demonstrates the simplest use of the AND function with two conditions.

=AND(A1>10, B1<20)

This formula checks if cell A1 is greater than 10 AND cell B1 is less than 20. It returns TRUE only if both conditions are met. Otherwise, it returns FALSE.

Basic OR Example

This example shows the simplest use of the OR function with two conditions.

A	B	C
15	25
		=OR(A1>20, B1>20)

The table demonstrates how OR evaluates conditions. In this case, since B1 (25) is greater than 20, the formula returns TRUE even though A1 (15) is not.

=OR(A1>20, B1>20)

This formula checks if either A1 is greater than 20 OR B1 is greater than 20. It returns TRUE if at least one condition is true. Here, it returns TRUE because B1 meets the condition.

Combining AND with IF

AND is often nested within IF to create more complex conditional statements. This example shows a practical application.

A	B	C
85	90
		=IF(AND(A1>=80, B1>=80), "Pass", "Fail")

The table demonstrates using AND within an IF statement. The formula checks if both scores (A1 and B1) are 80 or above to determine a Pass/Fail result.

=IF(AND(A1>=80, B1>=80), "Pass", "Fail")

This formula returns "Pass" only if both A1 and B1 are 80 or higher. Otherwise, it returns "Fail". Here, it returns "Pass" since both conditions are met.

Combining OR with IF

OR can also be nested within IF for different conditional logic. This example shows a practical use case.

A	B	C
75	65
		=IF(OR(A1>=80, B1>=80), "Qualified", "Not Qualified")

The table shows how OR works within an IF statement. The formula checks if either score meets the qualification threshold of 80 points.

=IF(OR(A1>=80, B1>=80), "Qualified", "Not Qualified")

This formula returns "Qualified" if either A1 or B1 is 80 or higher. Here, it returns "Not Qualified" since neither score meets the condition. OR provides more flexible criteria than AND.

Combining AND and OR Together

For complex logic, you can combine AND and OR in the same formula. This example demonstrates this advanced technique.

A	B	C
35	95
		=IF(OR(AND(A1>=50, A1<=100), AND(B1>=50, B1<=100)), "Valid", "Invalid")

The table shows a complex condition checking if either value falls within a specific range (50-100). The formula uses both AND and OR to create this logic.

=IF(OR(AND(A1>=50, A1<=100), AND(B1>=50, B1<=100)), "Valid", "Invalid")

This formula returns "Valid" if either A1 or B1 is between 50 and 100 (inclusive). Here, it returns "Valid" because B1 meets the condition, demonstrating how to combine these logical functions.

AND/OR with Multiple Conditions

Both AND and OR can handle more than two conditions. This example shows AND with three conditions.

=AND(A1>0, B1>0, C1>0)

This formula checks if all three cells (A1, B1, and C1) contain positive numbers. It returns TRUE only if all three conditions are true. This demonstrates the functions' scalability.

AND/OR with Text Values

AND and OR can evaluate text conditions using comparison operators. This example demonstrates text comparisons.

A	B	C
Completed	Approved
		=AND(A1="Completed", B1="Approved")

The table shows how AND can evaluate text matches. The formula checks if both status fields meet specific text criteria for a process to continue.

=AND(A1="Completed", B1="Approved")

This formula returns TRUE only if A1 contains "Completed" AND B1 contains "Approved". Text comparisons are case-insensitive in Excel by default. This shows the functions' versatility.

AND/OR in Conditional Formatting

AND and OR are frequently used in conditional formatting rules. This example shows a practical application.

=OR(A1>B1, A1


This conditional formatting formula highlights cells where A1 is either greater 
than B1 or less than C1. The OR function allows multiple conditions to trigger 
the same formatting. This demonstrates their use in visual data analysis.



Common Errors with AND/OR


Several common mistakes occur when using AND/OR functions. This section 
explains these pitfalls and how to avoid them.



  

    
Error
    
Description
    
Solution
  
  

    
#VALUE!
    
Non-logical argument provided
    
Ensure all arguments evaluate to TRUE/FALSE
  
  

    
Incorrect nesting
    
Improper function combination
    
Check parentheses and logic flow
  
  

    
Overcomplication
    
Too many nested functions
    
Break into simpler, helper formulas
  



This table outlines frequent issues when working with AND/OR functions. 
Understanding these helps create more reliable formulas and troubleshoot 
problems effectively.



The AND and OR functions are essential for creating 
complex logical tests in Excel. They work well individually but become 
particularly powerful when combined with IF or used in conditional formatting. 
Mastering these functions will significantly enhance your ability to analyze 
data and automate decision-making in spreadsheets.


Author


My name is Jan Bodnar, and I am a passionate programmer with extensive
programming experience. I have been writing programming articles since 2007.
To date, I have authored over 1,400 articles and 8 e-books. I possess more
than ten years of experience in teaching programming.



List all Excel Formulas.