• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About

Excel AND Function and OR Function

The AND and OR functions in Excel both belong to the logical function category in the Excel function list.

These functions test multiple conditions and return the logical value TRUE or FALSE dependent upon the result of their logical tests.

Returning TRUE or FALSE is often not sufficient on its own, so these functions are often combined with the IF function in Excel or used with a feature such as Conditional Formatting or Data Validation.

Download the sample file for practise.

AND Function

The AND function in Excel only returns TRUE if all conditions are met. If any condition fails, the AND function returns FALSE.

The syntax for the Excel AND function is:

AND(logical_1, [logical_2], ...)

Only one condition is mandatory, but AND can handle up to 255 conditions.

The following AND formula tests the values in columns A and B. Both criteria must be met for the result to be TRUE.

=AND(A2="Platinum",B2>=5)
Excel AND function testing two conditions

In this example, only the last formula returns TRUE, as it is the only row where the text string in column A is equal to “Platinum” and the value in column B is greater than or equal to 5.

OR Function

The OR Excel function returns TRUE if one or more conditions are met. OR will return FALSE only if all conditions fail.

The syntax for the Excel OR function is:

OR(logical_1, [logical_2], ...)

The following OR formula tests the values in columns A and B. Only one condition needs to be TRUE for the formula to return TRUE.

=OR(A2="Platinum",B2>=5)
Excel OR formula testing two columns

In this example, only the third row of the range returns FALSE. This is the only row where neither condition was met. Column A does not equal “Platinum” and column B is not greater than or equal to 5.

Advanced Excel Formulas book

Combined with the IF Function

A key role of these functions is as a nested function with IF, as on its own, the IF function can perform only a single logical test.

In this Excel formula, the AND function is used to test if both criteria are TRUE, and if so, the IF function calculates a 10% discount, otherwise, the price remains the same.

=IF(AND(A2="Platinum",B2>=5),C2*(1-10%),C2)
Using the AND function with the IF function in Excel

Only the fourth and final row of the range has a 10% discount applied in the result. This is because the AND function returns TRUE when both arguments (Platinum and >=5) evaluate to TRUE.

In this article, you can see examples of OR being used with the IF function for partial text matches. This is a cool technique, as when you perform logical tests on text values, an exact match is required.

Excel AND Function for the Between Condition

The AND function is used to perform the between condition in Excel. For example, to test if a single value is between 60 and 90. This may be an important range for a business KPI to be within.

This formula tests if the value in column A is in the specified range of 60 to 90. If so, the IF function returns “Yes”, otherwise an empty cell is shown.

=IF(AND(A2>=60,A2<=90),"Yes","")
Between logic with the AND function in Excel

There are many other examples to require between logic including to work with age ranges and date ranges.

Using the VLOOKUP function in Excel as a range lookup is another neat way to perform the between condition in Excel.

OR and the AND Function Together

Using both functions together can get complex, but it enables us to create stronger logic in our formulas.

The following formula uses OR to return TRUE if the tier is either “Platinum” or “Gold”. The AND function uses the result from OR and the function tests if the sessions are greater than or equal to 5.

The If function applies a 10% discount only if the AND function returns TRUE. In this example, that is the first and last rows of the range only.

=IF(
AND(OR(A2="Platinum",A2="Gold"),B2>=5),
C2*(1-10%),C2
)
OR and AND function combined in a formula

Multiple Conditions with Conditional Formatting

You can also use the AND function inside a Conditional Formatting rule. Both functions are not limited to returning values to the sheet.

In the following video, the AND function (and OR) are used to test multiple columns and highlight the rows where the functions return TRUE. A COUNTIF function example is also included in the video.

What Does <> Mean in Excel?

The <> symbol means “not equal to”. For example, the formula =A2<>”London” tests if the value in cell A2 is not equal to “London”. Therefore, the formula returns TRUE if cell A2 contains any value except “London”.

Logical values returned by not equal to logical expression

Microsoft Excel also has the NOT function to perform a “not equal to” logical test.

Of course, this logic can be used with an OR or AND function in Excel.

Wrap Up

In this tutorial, we have seen a few examples of the AND and OR Microsoft Excel functions to show how useful they are.

In my Advanced Excel Formulas book, you will learn more than 150 of the best Excel functions supported with over 500 examples. Practise files are included.

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·