AND and OR in Excel: Combine Conditions Inside IF

Pass needs a score of 60 or more AND attendance of 80% or more. A discount applies if the customer is a member OR the order is over $500. Single-condition IF cannot express either rule. AND and OR are the two functions that combine conditions, and they slot inside IF so one formula can test the whole rule at once.

What AND / OR does

AND returns TRUE only when every condition inside it is true; OR returns TRUE when at least one is. They do not make decisions themselves — they produce a single TRUE or FALSE that IF (or COUNTIFS, or conditional formatting) can act on. You can nest them, too: AND inside OR, OR inside AND, as deep as the rule requires.

The syntax

=IF(AND(A2>=60, B2>=80%), "Pass", "Fail")

  • logical1, logical2, … — the conditions to combine, for example A2>=60 and B2>=80%. AND needs all true; OR needs one true.
  • IF(test, value_if_true, value_if_false) — the decision that uses the TRUE/FALSE result.

Step-by-step example

Decide pass or fail from a score and an attendance rate.

  1. Click the cell where the result should go.
  2. Type =IF(AND(B2>=60,C2>=80%),"Pass","Fail") — B is the score, C is attendance as a percentage.
  3. Press Enter, then copy down. Only rows passing both tests show Pass.
  4. For an either-or rule, swap in OR: =IF(OR(B2>=90,C2>=95%),"Honours","—") rewards a top score or top attendance.
  5. To combine them, nest: =IF(AND(B2>=60,OR(C2>=80%,D2="Exempt")),"Pass","Fail") passes students who meet the score and either attend or are exempt.

Worked example

Name Score Attendance Result
Mina 92 95% Pass
Jae 58 90% Fail
Sora 77 70% Fail
Tom 84 88% Pass

Jae fails on score despite good attendance, and Sora fails on attendance despite a good score — AND demands both. Tom passes both tests. One formula, one clear rule, no helper columns.

Common errors

Everything returns FALSE. Check the comparison operators and the data types. Comparing a percentage stored as text (“80%”) against 80% never matches — the cell must hold a real number formatted as a percentage.

OR passes rows it should not. That is what OR does — one true condition is enough. If you meant “both”, you wanted AND. Re-read the rule in plain words before choosing.

#VALUE! from a blank cell. A comparison against a blank cell can error inside AND/OR. Guard it with IFERROR or test the cell first: AND(B2<>"",B2>=60).

Brackets in the wrong place. AND and OR take the conditions; IF takes the result. =IF(AND(B2>=60,C2>=80%),"Pass","Fail") is right — putting the text inside AND is not.

Tip

When a rule has more than two or three conditions, write it in plain English first, then translate word by word: “and” becomes AND, “or” becomes OR, “either” usually means OR. If the English is confusing, the formula will be too — simplify the rule before you type it.

AND and OR in conditional formatting

AND and OR are just as useful outside IF. Select a table, open Conditional Formatting, choose “Use a formula”, and type =AND($B2>=60,$C2<80%) to highlight every row that passed the exam but missed the attendance target — the rows that need attention. The dollar signs lock the columns while the row number stays relative, so the rule tests each row against its own values. One formula rule can replace a whole column of helper flags, and the highlighting updates itself whenever the data changes.

Practice file

Download the AND / OR practice file (.xlsx) — it contains a pass/fail sheet with AND and OR formulas, plus a blank sheet for you to try it yourself.

Google Sheets

Google Sheets supports AND and OR with the same syntax. Sheets also lets you use them directly in conditional formatting rules, which is the fastest way to highlight rows that pass a multi-condition test.

Example prices and figures are illustrative, not market data.