How to use the Excel IF function

Summary

The IF function can perform a logical test and return 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.

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.

Usage notes

Use the IF function to test for or evaluate certain conditions, and then react differently depending on whether the test was TRUE or FALSE.

In the example shown, 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 this formula could be reversed. A formula with the same result could be written like this:

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

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

Either formula above, when copied down the column, will test every score and return the correct result.

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

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 an 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 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

More information

Notes:

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

Related functions

Excel COUNTIF Function

COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,...

Excel SUMIF Function

SUMIF is a function to sum cells that meet a single criteria. SUMIF can be used to sum cells based on dates, numbers, and text that match specific criteria. SUMIF supports logical operators (>,,=) and wildcards (*,?) for partial matching....

Excel AND Function

The 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 Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.