AND, OR and NOT in Excel With IF

AND, OR and NOT let one formula check several conditions at once. AND asks "are all of these true?", OR asks "is at least one true?", and NOT flips an answer. The Excel AND OR function pair is most useful inside IF, where it decides things like who gets a bonus or which orders need a check. This guide uses one small sales table throughout.

What do AND, OR and NOT return?#

All three return TRUE or FALSE. A condition is any test with a yes-or-no answer, such as C2>=50000.

Each condition is a comparison, built with signs like >=, < and <>. If those are new, read the guide to Excel comparison operators first.

Here is the data. This quarter's targets are sales of at least 50000 and a rating of at least 4.5.

How to use AND in Excel#

AND returns TRUE only if every condition is TRUE. One FALSE makes the whole answer FALSE.

=AND(C2>=50000,D2>=4.5)

Lena hits both targets, so she gets TRUE. Raj has the sales but a 4.1 rating, so he gets FALSE. Mei has exactly 4.5, and >= includes it, so she gets TRUE. Type 50000 without a comma: inside a formula, a comma separates the conditions.

AND is also the standard way to check whether a value is between two numbers:

=AND(B2>=10,B2<=20)

This is TRUE for any value from 10 to 20, both ends included. Use > and < instead if the ends should be left out. The same pattern works for dates, because Excel stores dates as numbers:

=AND(A2>=DATE(2025,1,1),A2<=DATE(2025,3,31))

This is TRUE for any date in the first quarter of 2025. Build the dates with DATE rather than typing "1/1/2025" in quotes, which is text and won't compare correctly.

How to use OR in Excel#

OR returns TRUE if at least one condition is TRUE. It is FALSE only when every condition is FALSE.

=OR(C2>=50000,D2>=4.5)

Raj and Sofia each hit one target, so both get TRUE. Only Ben, who missed both, gets FALSE.

A common mistake is writing OR like a sentence:

=OR(B2="North","South")

This does not check for South. Each condition must be a complete test, so write =OR(B2="North",B2="South"). Excel can't treat the word "South" on its own as TRUE or FALSE, so the short version gives a #VALUE! error instead.

How to use NOT in Excel#

NOT flips one value: TRUE becomes FALSE and FALSE becomes TRUE.

=NOT(B2="North")

This is TRUE for every rep outside North. For a single test, B2<>"North" does the same job and is shorter. NOT earns its place around AND or OR: NOT(OR(...)) means "none of these is true", and NOT(AND(...)) means "not all of these are true".

NOT is also handy with the IS functions. =NOT(ISBLANK(C2)) is TRUE when C2 has something in it, which is a clear way to write "this row has been filled in".

How to use AND, OR and NOT with IF#

On their own, these functions only show TRUE or FALSE. Put them in IF's test to show a label or run a calculation:

=IF(AND(C2>=50000,D2>=4.5),"Bonus","No bonus")
=IF(OR(C2<40000,D2<4),"Review","OK")
=IF(NOT(OR(C2>=50000,D2>=4.5)),"Coach","OK")

Read the brackets carefully: the AND or OR closes before the comma that starts IF's results. In the third formula, NOT( and OR( each need a ) before "Coach". If IF itself is new, see the Excel IF function explained simply.

Using the Excel AND OR function together in one test#

You can nest OR inside AND, or AND inside OR, to build rules with "and" and "or" in them. Say a bonus needs sales of at least 50000 and the rep must work in North or South:

=IF(AND(C2>=50000,OR(B2="North",B2="South")),"Bonus","No bonus")

Excel works out the inner OR first, then the AND. Lena (North, 54000) and Mei (South, 58000) get Bonus. Raj has 61000 in sales but works in East, so the OR is FALSE and so is the AND.

AND and OR work on one row at a time. To count or add up the rows that meet several conditions, use COUNTIFS or SUMIFS instead: =COUNTIFS(C2:C6,">=50000",D2:D6,">=4.5") counts the reps who hit both targets (2 here). Each extra pair of range and criteria works like another condition inside AND. See COUNTIFS and SUMIFS with several conditions.

When a rule has many "or" options on the same cell, like ten region names, a lookup list is easier to maintain. For turning codes into labels, see the SWITCH function.

What are the most common mistakes?#

These cause most wrong answers:

If a condition refers to a cell that holds an error, AND and OR return that error too. Fix the source cell, or see IFERROR, used the right way for when hiding it makes sense.

Current Excel versions accept up to 255 conditions in one AND or OR. There is also XOR (Excel 2013 and later), which returns TRUE when an odd number of conditions are TRUE. With two conditions, that means exactly one. For the full IF syntax, see the IF function guide.

Practise it#

Try the Review exercise on this page, then practise AND, OR and NOT in a live spreadsheet. The lesson uses the same sales reps, and you build each check yourself, ending with a NOT(OR(...)) coaching flag.