← Google Sheets from Scratch: Formulas, QUERY, and Collaboration
Lesson
Lesson 2: AND, OR, and compound conditions
Combine several conditions with AND and OR inside IF.
AND, OR, and how to use them in IF
AND, OR, and how to use them in IF
Sometimes one condition in IF isn’t enough. The AND function returns TRUE only when all the listed conditions are true at the same time. The OR function returns TRUE if at least one of the conditions is true. Both take any number of arguments separated by commas.
Most often, AND and OR go right inside the first argument of IF: =IF(AND(A2>0, B2<100), "In range", "Out of range"). Here the cell shows “In range” only if A2 is greater than zero AND B2 is less than 100 — both conditions together.
A practical example with AND: a 15% discount goes only to regular customers (C2="Regular") with a purchase amount over 5,000 (D2>5000). Formula: =IF(AND(C2="Regular", D2>5000), "15% off", "No discount"). If even one condition isn’t met, there’s no discount.
An example with OR: free shipping if the amount is over 3,000 OR the customer is a VIP: =IF(OR(D2>3000, C2="VIP"), "Free shipping", "Paid shipping"). One match out of two is enough to get free shipping. When choosing between AND and OR, go by the wording: “both … and …” means AND; “either … or …” means OR.
Lesson notes
AND, OR, and how to use them in IF
Sometimes one condition in IF isn’t enough. The AND function returns TRUE only when all the listed conditions are true at the same time. The OR function returns TRUE if at least one of the conditions is true. Both take any number of arguments separated by commas.
Most often, AND and OR go right inside the first argument of IF: =IF(AND(A2>0, B2<100), "In range", "Out of range"). Here the cell shows “In range” only if A2 is greater than zero AND B2 is less than 100 — both conditions together.
A practical example with AND: a 15% discount goes only to regular customers (C2="Regular") with a purchase amount over 5,000 (D2>5000). Formula: =IF(AND(C2="Regular", D2>5000), "15% off", "No discount"). If even one condition isn’t met, there’s no discount.
An example with OR: free shipping if the amount is over 3,000 OR the customer is a VIP: =IF(OR(D2>3000, C2="VIP"), "Free shipping", "Paid shipping"). One match out of two is enough to get free shipping. When choosing between AND and OR, go by the wording: “both … and …” means AND; “either … or …” means OR.