The IF function in Excel, with AND and OR

IF lets the sheet make a decision: ask a question about a cell, give one answer for yes and another for no. Copied down a column, it sorts every row at once. The sheet above is the lesson; this page explains it, with an example worked out by the same formula engine.

What IF does

=IF(logical_test, value_if_true, value_if_false)

logical_test

A comparison that is either TRUE or FALSE, such as C2>0. The comparisons are = (equal), <> (not equal), >, <, >= and <=.

value_if_true

What the cell shows when the test holds. Text goes in double quotes: "Late". A number or another formula doesn't need them.

value_if_false

What it shows otherwise. Leave it out and the cell shows FALSE, which is rarely what you want.

A worked example

This sheet comes from the lesson, set in the Festival bar: unpaid invoices, with how many days late each one is.

In D2, =IF(C2>0,"Late","On time") asks whether the days late are above 0, and gives Late. Filled down, every row gets its own answer. Notice the rows with 0 days: 0 isn't above 0, so they are on time. Write C2>=0 and every row would count as late; where the line falls is the whole question.

Two conditions: AND and OR

=AND(test1, test2, …) is TRUE only when every test holds. =OR(test1, test2, …) is TRUE when at least one does. Either goes inside IF as its test.

The rule here: phone only when an invoice is more than 14 days late and also for an amount big enough to matter, the figure written into the formula. In E2, =IF(AND(C2>14,B2>=3000),"Call","Email") gives Email.

Swap AND for OR and the rule changes completely: =IF(OR(C2>14,B2>=3000),"Call","Email") gives Call for the same row, because one of the two is enough for OR. That's the mistake the lesson names. Read the rule out loud with "and" or "or" in it, and the right function is the word you said.

More than two answers

For three outcomes, put a second IF where the "no" answer goes: =IF(C2>30,"Call",IF(C2>0,"Email","OK")). Excel checks them in order and stops at the first that holds, so start with the strictest. IFS does the same without the nesting, in Excel 2019 and later. More than three levels is usually a sign that a small lookup table would be clearer.

The lesson, step by step

1. 1 Let the sheet decide

   In D2: =IF(C2>0,"Late","On time") gives Late.

2. 2 Copy the decision down

   Fill D2 down over D2:D8. The last one, D8, becomes =IF(C8>0,"Late","On time").

   They give On time Late On time Late On time On time

3. 3 Two conditions at once

   In E2: =IF(AND(C2>14,B2>=3000),"Call","Email") gives Email.

   Named mistake: =IF(OR(C2>14,B2>=3000),"Call","Email") gives Call. That's OR: one of the two is enough. AND needs both to be true.

4. 4 Copy it down

   Fill E2 down over E2:E8. The last one, E8, becomes =IF(AND(C8>14,B8>=3000),"Call","Email").

   They give Email Call Email Email Email Email

In real Excel

Text comparisons ignore capitals: ="late"="Late" is TRUE. Straight double quotes only; curly quotes pasted from a document give an error. A blank cell counts as 0 in a comparison with a number, so an empty row is "on time" by the rule above. If that matters, test for blanks first with =IF(C2="","",…).

Keep going

1. 6
   Next up is Conditions: SUMIF and COUNTIFTotals and counts that only include the rows you ask for.
2. 6
   Conditions: SUMIF and COUNTIFTotals and counts that only include the rows you ask for.
3. 3
   Percentages: percentage formulaWork out percentage change, then plan next year with a growth rate.