Excel IF Function

IF tests a condition and returns one value when it is true and another when it is false. Write =IF(condition, value_if_true, value_if_false), for example =IF(B2>=50, "Pass", "Fail").

=IF(logical_test, [value_if_true], [value_if_false])

logical_test: The condition to check, such as B2>=50 or A2="Paid". It is either true or false.
value_if_true: What to return when the condition is true. Text must be in double quotes, numbers and cell references are not.
value_if_false: What to return when the condition is false. If you leave it out, Excel returns FALSE. Use "" to return an empty-looking cell.

How IF works

IF is a fork in the road. Excel checks a condition, then takes one of two paths. The condition is a comparison, and a comparison always comes out as true or false.

In the sheet below, column B holds exam scores and the pass mark is in J2. To show whether the score in B2 is a pass:

=IF(B2>=$J$2, "Pass", "Fail")

Excel compares B2 with the pass mark. With a score of 50 and a pass mark of 50, 50>=50 is true.
Because it is true, IF returns the second argument, "Pass".
If the score were 49, the comparison would be false and IF would return "Fail".

The dollar signs in $J$2 lock the pass mark, so when you fill the formula down the column, the score moves to B3, B4 and so on, while the pass mark stays in J2.

Comparison operators

= equal to, <> not equal to
> greater than, < less than
>= greater than or equal to, <= less than or equal to

Pay attention to the boundary. "50 or more passes" is >=50. Writing >50 silently fails everyone who scored exactly 50.

Text and number results

The two results can be text, numbers, cell references or even other formulas. Text goes in double quotes: "Pass". Numbers do not: =IF(B2>=50, 10, 0). Without quotes, Excel reads Pass as a name and shows #NAME?. Text comparisons ignore capital letters, so A2="paid" is true when A2 contains Paid.

Two conditions: AND and OR

One IF can test several conditions when you wrap them in AND or OR.

AND(test1, test2) is true only when all tests are true.
OR(test1, test2) is true when at least one test is true.

=IF(AND(B2>=J2, C2>=J3), "Yes", "No")

This returns "Yes" only when the score reaches the pass mark and the attendance reaches the minimum.

More than two outcomes: nested IF and IFS

The false argument can hold another IF. Excel then asks the questions one after another and stops at the first true one:

=IF(B2>=85, "A", IF(B2>=70, "B", IF(B2>=50, "C", "F")))

Order matters. Start with the strictest condition, here the highest score. A score of 90 would match every test, so the first test that is true wins. If you started with B2>=50, every passing score would get a C.

In Excel 2019 and later, IFS reads more clearly. It takes pairs of a test and a result. There is no automatic "otherwise", so finish with TRUE as a test that always matches:

=IFS(B2>=85, "A", B2>=70, "B", B2>=50, "C", TRUE, "F")

Leaving a cell empty

Two double quotes with nothing between them, "", is an empty text value that shows as a blank cell. It is the usual way to say "show nothing". To test whether a cell is empty, compare it with "": =IF(C2="", "", C2-B2).

Practise IF

Click a numbered cell, type a formula and press Enter. Each answer is checked straight away, then tested again on changed data, so the result has to come from the formula. While typing a formula, click or drag across cells to insert their references. Yellow cells are inputs you can change.

	A	B	C	D	E	F	G	I	J
1	Student	Score	Days present	Result	Exam entry	Follow-up	Grade	Setting	Value
2	Dan	50	15					Pass mark	50
3	Ana	72	19					Min. days	15
4	Ben	45	20
5	Cleo	88	12
6	Eva	95	18
7	Finn	61	14
8	Gus	83	17
9	Hana	39	16
10
11	Student	First try	Retest	Improvement
12	Ben	45	58
13	Dan	50
14	Finn	61	66
15	Hana	39
16
17

0 of 5 done

1
In D2, show Pass when the score in B2 reaches the pass mark in J2, and Fail when it does not. It is filled down to D9 for you, and all eight rows must be right.

Show answer
=IF(B2>=$J$2,"Pass","Fail")
2
A student may sit the exam only if the score in B2 reaches the pass mark in J2 and the days present in C2 reach the minimum in J3. In E2, return Yes or No.

Show answer
=IF(AND(B2>=J2,C2>=J3),"Yes","No")
3
A student needs a follow-up call if the score in B2 is below the pass mark in J2 or the days present in C2 are below the minimum in J3. In F2, return Call or OK.

Show answer
=IF(OR(B2<J2,C2<J3),"Call","OK")
4
Write one formula in G2 that gives a grade for the score in B2: A for 85 or more, B for 70 or more, C for 50 or more, otherwise F. It is filled down to G9, and all eight rows must be right.

Show answer
=IF(B2>=85,"A",IF(B2>=70,"B",IF(B2>=50,"C","F")))
5
In D12, work out the improvement: the retest score in C12 minus the first try in B12. Some students have not done the retest yet, and for them the cell must stay empty, not show 0 or a negative number. It is filled down to D15.

Show answer
=IF(C12="","",C12-B12)

Common IF problems

#NAME?

Text without quotation marks. =IF(B2>=50, Pass, Fail) makes Excel look for names called Pass and Fail. Write "Pass" and "Fail".

FALSE appears instead of a result

The condition was false and the third argument is missing. This often happens at the end of a nested IF. Add the final result, even if it is just "".

Everyone gets the same result in a nested IF

The tests are in the wrong order. Excel stops at the first true test, so put the strictest condition first.

Numbers in quotes do not behave like numbers

Comparing with "50" in quotes compares with text. Write B2>=50, without quotes around the number.

Result is wrong only at the boundary

Check > against >= and < against <=. Test the formula with a value that is exactly on the limit.

Too many IFs

Excel allows 64 levels of nesting, but anything beyond three or four is hard to read and fix. Use IFS, or a lookup table with VLOOKUP or XLOOKUP.

Questions

How do I use IF with multiple conditions?

Wrap the conditions in AND (all must be true) or OR (at least one must be true) inside the first argument: =IF(AND(B2>=50, C2>=15), "Yes", "No").

How do I write IF with text?

Put the text in double quotes, both in the test and in the results: =IF(A2="Paid", "Done", "Chase"). The comparison is not case-sensitive.

What does IF return when the condition is false and there is no third argument?

It returns FALSE. If you want a blank instead, write "" as the third argument.

What is the difference between nested IF and IFS?

They give the same result. A nested IF puts one IF inside the false argument of another. IFS lists test and result pairs in a row, which is easier to read. IFS needs Excel 2019 or later, and it returns #N/A when no test is true, so end it with TRUE as a catch-all.

How do I check whether a cell is empty?

Use =IF(A2="", "empty", "has a value"), or =IF(ISBLANK(A2), ...). ISBLANK is false for a cell that holds a formula returning "", while A2="" is true for both.

How do I count or sum with a condition?

Use COUNTIF and SUMIF instead of filling a column with IF formulas. They count or add only the rows that meet the condition.

Excel IF Function

How IF works

Comparison operators

Text and number results

Two conditions: AND and OR

More than two outcomes: nested IF and IFS

Leaving a cell empty

Practise IF

Common IF problems

#NAME?

FALSE appears instead of a result

Everyone gets the same result in a nested IF

Numbers in quotes do not behave like numbers

Result is wrong only at the boundary

Too many IFs

Questions

How do I use IF with multiple conditions?

How do I write IF with text?

What does IF return when the condition is false and there is no third argument?

What is the difference between nested IF and IFS?

How do I check whether a cell is empty?

How do I count or sum with a condition?

Related lessons