Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Example of COUNTIFS with variable table column in Excel
  • How To Filter Data in Excel
  • How to create a Histogram in Excel
  • Filter Data Based on Date in Excel
  • How To Perform and Interpret Regression Analysis in Excel

References

  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FORMULATEXT function
  • Multi-criteria lookup and transpose in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel VLOOKUP Function

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation must not contain
  • Excel Data validation require unique number
  • Excel Data validation allow weekday only
  • Excel Data validation unique values only

If this AND that OR that in Excel

by

This tutorial shows how to calculate If this AND that OR that in Excel using the example below;

Formula

=IF(AND(A1="x",OR(B1="y",B1="z")),"x","")

Explanation

To test for various combinations of this AND that, or this OR that, you can use the IF function with the AND and OR functions. In the example shown, the formula in D6 is:

=IF(AND(B6="red",OR(C6="small",C6="medium")),"x","")

When an item is “red”, and either “small” or “medium”, the formula returns “x”. All other combinations return an empty string (“”).

Worked Example:   Invoice status with nested if in Excel

How this formula works

In the example shown, we simply want to “mark” or “flag” rows where the color is “red” AND size is either “small” or “medium”. To return TRUE when items are red and small, we can use a logical statement constructed with the AND function:

AND(B6="red",C6="small")

To extend the statement to return TRUE when items are red and either small or medium, we can nest the OR function inside the AND function like this:

AND(B6="red",OR(C6="small",C6="medium"))

This snippet will return TRUE only if the value in B6 is “red” AND the value in C6 is either “small” or “medium”.  It is placed inside the IF function as the logical test. When the logical test returns TRUE, the IF function returns “x”. When the logical test returns FALSE, the IF function returns an empty string (“”).

Worked Example:   Basic outline numbering in Excel

Change the values returned by IF to suit your needs.

Post navigation

Previous Post:

If this AND that in Excel

Next Post:

IF with wildcards in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Nested IF function example in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Extract date from a date and time in Excel
  • Check If Two Dates are same month in Excel
  • Add months to date in Excel
  • Convert date to Julian format in Excel
  • Convert Excel time to decimal seconds

Grouping

  • How to randomly assign data to groups in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel
  • Calculate conditional mode with criteria in Excel
  • Categorize text with keywords in Excel

General

  • 44 Practical Excel IF function Examples
  • How to Create Calendar in Excel
  • Count cells that do not contain errors in Excel
  • How to create dynamic named range with INDEX in Excel
  • How to test a range for numbers in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning