How to use the Excel IF function

Summary

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR to extend the logical test.

Purpose

Test for a specific condition

Return value

The values you supply for TRUE or FALSE

Syntax

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

Arguments

logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
value_if_true - [optional] The value to return when logical_test evaluates to TRUE.
value_if_false - [optional] The value to return when logical_test evaluates to FALSE.

Version

Excel 2003

Usage notes

The IF function is used to run a logical test, and reacts differently depending on whether the result is TRUE or FALSE. The first argument, logical_test, is an expression that returns either TRUE or FALSE. Both value_if_true and value_if_false are optional, but at least one of them must be provided. The result from IF can be a value, a cell reference, or even another formula.

In the example shown above, we want to assign either "Pass" or "Fail" based on a test score. A passing score is 70 or higher. The formula in D6, copied down, is:

=IF(C6>=70,"Pass","Fail")

Translation: If the value in C6 is greater than or equal to 70, return "Pass". Otherwise, return "Fail".

The logical flow of this formula can be reversed. The formula below returns the same result:

=IF(C6<70,"Fail","Pass")

Translation: If the value in C6 is less than 70, return "Fail". Otherwise, return "Pass".

Both formulas above, when copied down, will return correct results.

Note: If you are new to the idea of formula criteria, this article explains many examples.

Another formula

The IF function can return another formula as a result. For example, the formula below will return A1*5% when A1 is less than 100, and A1*7% when A1 is greater than or equal to 100:

=IF(A1<100,A1*5%,A1*7%)

Nested IF statements

The IF function can be "nested". A "nested IF" refers to a formula where at least one IF function is nested inside another in order to test for more conditions and return more possible results. Each IF statement needs to be carefully "nested" inside another so that the logic is correct.

For example, the following formula can be used to assign a grade rather than a pass / fail result:

=IF(C6<70,"F",IF(C6<75,"D",IF(C6<85,"C",IF(C6<95,"B","A"))))

Up to 64 IF functions can be nested. However, in general, you should consider other functions, like VLOOKUP or HLOOKUP for more complex scenarios, because they can handle more conditions in a much more streamlined fashion.

Logical operators

When you are constructing a test with IF, you can use any of the following logical operators:

Comparison operator	Meaning	Example
=	equal to	A1=D1
>	greater than	A1>D1
>=	greater than or equal to	A1>=D1
<	less than	A1
<=	less than or equal to	A1<=D1
<>	not equal to	A1<>D1

The IF function doesn't support wildcards, but you can combine IF with COUNTIF to get basic wildcard functionality.

IF with AND, OR, NOT

The IF function can be combined with the AND function and the OR function. For example, to return "OK" when A1 is between 7 and 10, you can use a formula like this:

=IF(AND(A1>7,A1<10),"OK","")

Translation: if A1 is greater than 7 and less than 10, return "OK". Otherwise, return nothing ("").

To return B1+10 when A1 is "red" or "blue" you can use the OR function like this:

=IF(OR(A1="red",A1="blue"),B1+10,B1)

Translation: if A1 is red or blue, return B1+10, otherwise return B1.

=IF(NOT(A1="red"),B1+10,B1)

Translation: if A1 is NOT red, return B1+10, otherwise return B1.

More information

See below for more IF function examples.

Notes

To count things conditionally, use the COUNTIF or the COUNTIFS functions.
To sum things conditionally, use the SUMIF or the SUMIFS functions.
If any of the arguments to IF are supplied as arrays, the IF function will evaluate every element of the array.

Related functions

Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...

Excel SUMIF Function

The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The SUMIF function supports logical operators (>,,=) and wildcards (*,?) for partial matching....

Excel AND Function

The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...

Excel OR Function

The OR function is a logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE. For example, to test A1 for either "x" or "y", use =OR(A1="x",A1="y"). The OR function can be used as the logical test inside the...