How to use the IF function
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
Excel function syntax
IF(logical_test, [value_if_true], [value_if_false])
Arguments
| logical_test | Required. The logical expression determines what value the IF function returns. Excel evaluates all numerical values positive or negative to TRUE except 0 (zero) that equals FALSE. |
| [value_if_true] | Optional. The value the IF function returns if the logical expression evaluates to TRUE. If omitted 0 (zero) is returned. |
| [value_if_false] | Optional. The value the IF function returns if the logical expression evaluates to FALSE. If omitted 0 (zero) is returned. |
Comment
The IF function is one of the most used functions in Excel.
Table of Contents
- Logical expression
- Logical operators
- Use an Excel function to perform a logical test
- Working with arrays
Logical expression
The logical_test argument in the IF function determines the outcome meaning if the second argument [value_if_true] or third argument [value_if_false] will be returned or calculated.
Boolean value TRUE or FALSE are valid outcomes, however, their numerical equivalents are also valid. For example, 0 (zero) is evaluated as FALSE and all other numerical values, even negative values, are evaluated as TRUE.
Formula in cell B3:
The logical_test argument in the formula above is 10/20 which equals 0.5. 0.5 is not equal to 0 (zero) so the formula returns TRUE in cell B3.
Formula in cell B4:
The logical_test argument in cell B4 above is 0 (zero). 0 (zero) equals 0 (zero) so the formula returns FALSE in cell B3.
Formula in cell B5:
The logical_test argument in the formula above is 1. 1 is not equal to 0 (zero) so the formula returns TRUE in cell B3.
Formula in cell B6:
The logical_test argument in cell B6 above is -1. -1 is not equal to 0 (zero) and the formula returns TRUE in cell B3.
The formula above returns #VALUE! error which means that text strings are not allowed as a logical test, however a logical expression using text strings are fine, see example below.
This formula will return TRUE because "A" is equal to "A".
Logical operators
The logical_test argument allows you to use comparison operators, these characters let you to do more advanced comparisons than the equal sign.
- = (equal sign)
- < less than
- > greater than
- <= less than or equal to
- >= greater than or equal to
- <> not equal
Formula in cell C3:
This formula compares the value in cell B3 with number 5, if equal TRUE is returned. Note that this formula is only for demonstration purposes, you can use this formula in cell C3 to get the same result =B3=5.
If you change the value in cell B3 the formula in cell C3 instantly recalculates and returns TRUE or FALSE based on the outcome of the logical_test.
Formula in cell C4:
The formula in cell C4 checks if the value in cell B4 is larger than 5, cell B4 contains 3 and 3 is not larger than 5 so the formula returns FALSE.
Formula in cell C5:
This formula evaluates if the value in cell B5 is smaller than 5, the image above shows that cell B5 contains number 6. 6 is not smaller than 5 and the formula returns FALSE.
Formula in cell C6:
The formula in cell C6 evaluates if the value in cell B6 is smaller than or equal to 5. Cell B6 contains 2, see image above, and 2 is smaller than or equal to 5. The formula returns TRUE in cell C6.
Formula in cell C7:
This formula checks if value in cell B7 is larger than or equal to 5, cell B7 contains 5 and 5 is equal to 5. The formula returns TRUE in cell C7.
Formula in cell C8:
The formula in cell C8 assesses if value in cell B8 is not equal to 5, cell B8 contains 5. 5 is equal to 5 and the formula returns FALSE in cell C8.
Use an Excel function to perform a logical test
You are not limited to comparison operators when dealing with the logical_test argument, any function that returns TRUE or FALSE can be used.
The picture above shows a formula in cell C3 that evaluates if cell B3 is blank. It is not blank so it returns FALSE.
Cell B4 is blank and the IF function returns TRUE in cell C4.
The following formula uses the ISNUMBER function to identify the value in cell B5.
The formula in cell C5 returns TRUE because the value in cell B5 is 6 and that is a number.
Cell C7 and C8 contain formulas that test if B7 and B8 contain a formula. Formula in cell C8 returns TRUE, cell B8 contains this formula =5+1.
Check out the functions in the Information category, many of them return TRUE or FALSE.
Working with arrays
The image above demonstrates a formula in cell F5 that uses the specified value in cell F3 to filter values from column C based column B.
If the year in column B matches the value in cell F3 the corresponding value on the same row is used. The formula is actually performing this calculation to all values in cell range B3:B10.
Array formula in cell F5:
This is an array formula, to enter an array formula press and hold CTRL + SHIFT simultaneously after you have entered the formula in a cell. Press Enter once and then release release all keys.
The formula changes and now has curly brackets surrounding the formula, like this: {=MEDIAN(IF(B3:B10=F3,C3:C10,""))}
Do not enter these characters your self, they appear automatically. Note that Office 365 subscribers do not need to enter this as an array formula, simply enter it a s a regular formula. This is a new feature in Excel called Dynamic arrays.
The IF function compares all values in cell range B3:B10 to the value in cell F3 and returns the corresponding value from cell range C3:C10 if they match. Nothing "" is returned if they don't match.
IF(B3:B10=F3,C3:C10,"")
becomes
IF({2012;2011;2012;2012;2011;2012;2012;2011}=2012, C3:C10, "")
becomes
IF({2012; 2011; 2012; 2012; 2011; 2012; 2012; 2011}=2012, C3:C10, "")
becomes
IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}, C3:C10, "")
becomes
IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}, {3; 76; 56; 51; 12; 6; 13; 36}, "")
and returns {3; ""; 56; 51; ""; 6; 13; ""}.
The MEDIAN function returns the middle value from the array.
MEDIAN(IF(B3:B10=F3,C3:C10,""))
becomes
MEDIAN({3; ""; 56; 51; ""; 6; 13; ""})
and returns 13 in cell F5.
This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.
Articles with the 'IF' Function
The following 7 articles have formulas that contain the IF function.
Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are [โฆ]
IF with AND function โ multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. [โฆ]
Use IF + COUNTIF to perform numerous conditions
The COUNTIF function allows you to construct a small IF formula that carries out plenty of logical expressions. Combining the IF [โฆ]
The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and [โฆ]
The formula above in cell D3 performs two different logical tests, if at least one of them is TRUE one [โฆ]
If cell contains text from list
The array formula in cell C3 checks if cell B3 contains at least one of the values in List (E3:E7), [โฆ]
If cell equals value from list
Regular formula Formula in cell C3: =IF(COUNTIF($E$3:$E$5,B3),"Yes","No") The COUNTIF function counts how many values in E3:E5 match cell B3, it returns [โฆ]
Functions in 'Logical'
The IF function function is one of many functions in the 'Logical' category.
The image above demonstrates the AND function with two logical expressions. If the value in column B is equal to [โฆ]
The FALSE function returns the logical (boolean) value FALSE. Excel Function Syntax FALSE() Arguments The FALSE function has no arguments. [โฆ]
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
How to use the IFERROR function
If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function [โฆ]
The IFNA function handles #N/A errors only, it returns a specific value if the formula returns a #N/A error. Formula [โฆ]
The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first [โฆ]
The NOT function returns the boolean opposite to the given argument. TRUE returns FALSE and FALSE returns TRUE. Formula in [โฆ]
The OR function allows you to carry out a logical test in each argument and if at least one argument returns [โฆ]
How to use the SWITCH function
The SWITCH function returns a given value determined by an expression and a list of values. If the expression matches [โฆ]
Returns the logical (boolean) value TRUE. Excel Function Syntax TRUE() Arguments The TRUE function has no arguments. Comments Excel interprets [โฆ]
The XOR function was introduced in Excel 2013 and calculates the logical exclusive OR meaning if at least one of the [โฆ]
13 Responses to โHow to use the IF functionโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
[...] Interested in how the IF function works, read this post: IF function explained [...]
[โฆ] IF(logical_test;[value_if:true];[value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE [โฆ]
[โฆ] IF(logical_test,[value_if_true],[value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE [โฆ]
[โฆ] IF(logical_test, [value_if_true],[value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE [โฆ]
[โฆ] IF(logical_test,[value_if:true], [value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE [โฆ]
Hi Oscar
Thank you for your explanation and examples. I'd really appreciate it if you could you help me with this:
I'm trying to say, "if a cell from E2 to E89 contains 1, then sum the contents of the corresponding cell in D", ie. I only want to add D cell contents together if the corresponding E cell contains '1'.
=SUM(IF(E2:E89=1, D2:D89, 0))
Thank you!
Alison.
[โฆ] If function [โฆ]
[โฆ] want to sort text values only, the IF and ISTEXT functions check if a value is a text [โฆ]
[โฆ] If the value in cell K23 is larger than cell K24 AND cell K24 is smaller than K25 THEN return the closing price. If not return nothing. Read more about IF function. [โฆ]
[โฆ] IF(logical_test, [value_if_true], [value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE [โฆ]
[โฆ] IF(logical_test, [value_if_true], [value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE [โฆ]
[โฆ] IF(logical_test;[value_if:true];[value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE [โฆ]
[โฆ] IF(logical_test,[value_if:true];[value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE [โฆ]