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
- What is a logical expression?
- Can a decimal number be used as a logical expression?
- Is 0 (zero) equivalent to boolean value FALSE in a logical expression?
- Is 1 equivalent to boolean value TRUE in a logical expression?
- Is -1 (negative number) equivalent to boolean value TRUE in a logical expression?
- What happens if a text string is used in the logical expression?
- What logical operators can you use in the logical expression?
- Use an Excel function to perform a logical test
- IF function - How to check if a cell is blank?
- IF function - How to check if a cell is not blank?
- IF function - How to check if a cell contains an error?
- IF function - How to check if a cell is a text value?
- IF function - How to check if a cell is a number?
- IF function - if false do nothing?
- IF function - greater than
- IF function - smaller than
- IF function - equals
- IF function - equals multiple values
- IF function - does not equal
- IF function - begins with
- IF function - ends with
- IF function - contains
- IF function - contains any text
- IF function - exact match case sensitive
- IF function - AND OR
- IF function between two sheets?
- IF function - copy cell value?
- IF function - add value?
- IF function - keep value?
- IF function - keep blank?
- If function based on cell color?
- How to work with multiple values in the IF function? (arrays)
- IF function - between two numbers?
- IF function and VLOOKUP? (link)
- Nested IF functions (link)
- SUMPRODUCT and IF functions (link)
- Get Excel *.xlsx file
1. What is a 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.
1.1 Can a decimal number be used as a logical expression?
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.
1.2 Is 0 (zero) equivalent to boolean value FALSE in a logical expression?
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.
1.3 Is 1 equivalent to boolean value TRUE in a logical expression?
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.
1.4 Is -1 (negative number) equivalent to boolean value TRUE in a logical expression?
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.
1.5 What happens if a text string is used in the logical expression?
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".
2. What logical operators can you use in the logical expression?
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.
3. 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.
4. IF function - How to check if a cell is blank?
The image above demonstrates the IF function in cell C3. It checks if the adjacent cell on the same row in column B is blank. If not blank the value itself is returned.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and paste it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The logical expression contains the equal sign. It checks if one value is equal to another value and returns boolean values TRUE or FALSE.
"" is the same as nothing or an empty cell.
B3=""
becomes
5=""
and returns FALSE. 5 is not equal to nothing.
Step 2 - Evaluate IF function
The second argument in the IF function is returned if the logical expression returns TRUE and the third is returned if FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(B3="","Blank",B3)
becomes
IF(FALSE,"Blank",B3)
becomes
IF(FALSE,"Blank",5)
and returns 5 or the value itself in cell B3.
5. IF function - How to check if a cell is not blank?
The image above demonstrates the IF function in cell C3. It checks if the adjacent cell on the same row in column B is not blank. If not blank the value itself is returned.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and paste it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The logical expression contains two logical operaters, the first one is smaller than and the seconmd is larger than. When you combine these you get not equal to.
"" is the same as nothing or an empty cell.
B3<>""
becomes
5<>""
and returns TRUE. 5 is not equal to nothing.
Step 2 - Evaluate IF function
The second argument in the IF function is returned if the logical expression returns TRUE and the third is returned if FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(B3<>"",B3,"Blank")
becomes
IF(TRUE,B3,"Blank")
becomes
IF(TRUE,5,"Blank")
and returns 5 or the value itself in cell B3.
6. IF function - How to check if a cell contains an error?
The image above demonstrates an IF function in cell C3. It checks if the adjacent cell on the same row in column B contains an error value. The formula returns "Error" if true and the value itself if false.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The ISERROR function returns TRUE if the cell contains an error and FALSE if not.
ISERROR(B3)
becomes
ISERROR(5)
and returns FALSE.
Step 2 - Evaluate IF function
The second argument in the IF function is returned if the logical expression returns TRUE and the third is returned if FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(ISERROR(B3), "Error", B3)
becomes
IF(FALSE, "Error", B3)
becomes
IF(FALSE, "Error", 5)
and returns 5.
7. IF function - How to check if a cell is a text value?
The image above shows an IF function in cell C3 that checks if the adjacent cell on the same row in column B is a text value. The formula returns "Text" if true and "Not text" if false.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
ISTEXT(B3)
becomes
ISTEXT(5)
and returns boolean FALSE.
Step 2 - Evaluate IF function
The ISTEXT function returns True if the argument is text and False if not.
IF(ISTEXT(B3), "Text", "Not text")
becomes
IF(False, "Text", "Not text")
and returns "Not text" in cell C3.
8. IF function - How to check if a cell is a number?
The image above shows an IF function in cell C3 that checks if the adjacent cell on the same row in column B is a text value. The formula returns "Text" if true and "Not text" if false.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The ISNUMBER function returns TRUE if the argument is a number and FALSE if not.
ISNUMBER(B3)
becomes
ISNUMBER(5)
and returns boolean TRUE.
Step 2 - Evaluate IF function
IF(ISTEXT(B3), "Text", "Not text")
becomes
IF(False, "Text", "Not text")
and returns "Not text" in cell C3.
9. IF function - if false do nothing?
The formula in cell C3 checks if the value in cell B3 is equal to 5, if so add 5 to the number in cell B3. If cell B3 is not equal to 5 do nothing.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The equal sign lets you compare two or more values, the expression returns TRUE or FALSE.
B3=5
becomes
5=5
and returns TRUE.
Step 2 - Evaluate IF function
The second argument is returned if the logical expression returns TRUE and the third argument is returned if FALSE. The third argument is "" which is nothing. The cell is empty. The IF function returns 0 (zero) if you leave the third argument empty.
IF(B3=5,B3+5,"")
becomes
IF(TRUE,B3+5,"")
becomes
IF(TRUE,5+5,"")
and returns 10 in cell C3.
10. IF function - greater than
The formula in cell C3 checks if the value in cell B3 is larger than cell C7, if so return the number in cell B3. If cell B3 is not larger than cell C7 then do nothing.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The larger than character lets you check if cell B3 is larger than cell C7, it returns TRUE if so. FALSE if not.
B3>$C$7
becomes
5>5
and returns FALSE.
Step 2 - Evaluate IF function
IF(B3>$C$7, B3, "")
becomes
IF(FALSE, B3, "")
becomes
IF(FALSE, 5, "")
and returns "" (nothing).
11. IF function - smaller than
The formula in cell C3 checks if the value in cell B3 is smaller than cell C7, if so return the number in cell B3. If cell B3 is not smaller than cell C7 then do nothing.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The smaller than character lets you check if cell B3 is smaller than cell C7, it returns TRUE if so. FALSE if not.
B3<$C$7
becomes
5<5
and returns FALSE.
Step 2 - Evaluate IF function
IF(B3<$C$7, B3, "")
becomes
IF(FALSE, B3, "")
becomes
IF(FALSE, 5, "")
and returns "" (nothing).
12. IF function - equals
The formula in cell C3 checks if the value in cell B3 is equal to value in cell C7, if so return "Yes". If cell B3 is not equal to cell C7 then return "No".
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The equal character lets you check if cell B3 is equal to the value in cell C7, it returns TRUE if so. FALSE if not.
B3=$C$7
becomes
5=4
and returns FALSE.
Step 2 - Evaluate IF function
IF(B3=$C$7, "Yes", "No")
becomes
IF(FALSE, "Yes", "No")
becomes
IF(FALSE, "Yes", "No")
and returns "No".
13. IF function - cell equals any of multiple values (OR logic)
The formula in cell C3 checks if the value in cell B3 is equal to any of the values in cell range C9:C11. If one value is equal then the formula returns "Yes" if not "No".
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
You can use logical operators to compare one value to multiple values, the result is an array with multiple boolean values TRUE or FALSE.
B3=$C$7:$C$11
becomes
"A"={"D";"C";"F"}
and returns
{FALSE; FALSE; FALSE}. "A" is not equal to "D", "C" or "F".
Step 2 - Evaluate OR function
The OR function returns TRUE if at least one boolean value in the array is TRUE. The OR function returns FALSE if all values in the array are FALSE.
OR(B3=$C$7:$C$11)
becomes
OR({FALSE; FALSE; FALSE})
and returns FALSE.
Step 3 - Evaluate IF function
IF(OR(B3=$C$7:$C$11), "Yes", "No")
becomes
IF(FALSE, "Yes", "No")
and returns "No".
Recommended links: If cell equals value from list | If cell contains text from list | IF with OR function | If cell contains text
14. IF function - does not equal
The formula in cell C3 checks if the value in cell B3 is not equal to the value in cell C7, if so return "Yes". If cell B3 is not equal to cell C7 then return "No".
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Logical expression
The less and greater than characters combined lets you check if cell B3 is not equal to the value in cell C7, it returns TRUE if so. FALSE if not.
B3<>$C$7
becomes
5<>4
and returns FALSE.
Step 2 - Evaluate IF function
IF(B3<>$C$7, "Yes", "No")
becomes
IF(TRUE, "Yes", "No")
and returns "Yes".
15. IF function - begins with
The formula in cell C3 checks if the value in cell B3 begins with the value in cell C7, if so return "Yes". If cell B3 does not end with value in cell C7 then return "No".
Note that the equal sign is not evaluating text strings with regard to upper and lower letters, in other words, the equal sign is not case sensitive.
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Count characters
The LEN function counts the number of characters in a string.
LEN($C$7)
becomes
LEN("Gr")
and returns 2.
Step 2 - Return k-th characters from left
The LEFT function returns a given number of characters from left.
LEFT(B3, LEN($C$7))
becomes
LEFT("Red", 2)
and returns "Re".
Step 3 - Logical test
LEFT(B3, LEN($C$7))=$C$7
becomes
"Re"="Gr"
and returns FALSE.
Step 4 - Evaluate IF function
IF(LEFT(B3, LEN($C$7))=$C$7, "Yes", "No")
becomes
IF(FALSE, "Yes", "No")
and returns "No" in cell C3.
16. IF function - ends with
The formula in cell C3 checks if the value in cell B3 ends with the value in cell C7, if so return "Yes". If cell B3 does not end with value in cell C7 then return "No".
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Count characters
The LEN function counts the number of characters in a string.
LEN($C$7)
becomes
LEN("ue")
and returns 2.
Step 2 - Return k-th characters from left
The RIGHT function returns a given number of characters from the right.
RIGHT(B3, LEN($C$7))
becomes
RIGHT("Red", 2)
and returns "ed".
Step 3 - Logical test
RIGHT(B3, LEN($C$7))=$C$7
becomes
"ed"="ue"
and returns FALSE.
Step 4 - Evaluate IF function
IF(RIGHT(B3, LEN($C$7))=$C$7, "Yes", "No")
becomes
IF(FALSE, "Yes", "No")
and returns "No" in cell C3.
17. IF function - cell contains a given string
The formula in cell C3 checks if the value in cell B3 contains the string in cell C7, if so return "Yes". If cell B3 does not contain the string in cell C7 then return "No".
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Check if value contains string
The COUNTIF function allows you to count cells matching a specific value, however, it also allows you to count cells containing a specific string.
You need to append the asterisk character before and after the string.
COUNTIF(B3, "*"&$C$7&"*")
becomes
COUNTIF("Red", "*r*")
and returns 1. This means that the string is found in cell B3. 1 is equal to TRUE and 0 (zero) is equal to FALSE.
Step 2 - Evaluate IF function
IF(COUNTIF(B3, "*"&$C$7&"*"), "Yes", "No")
becomes
IF(1, "Yes", "No")
and returns "Yes" in cell C3.
18. IF function - contains any text
The formula in cell C3 checks if the value in cell B3 is a number by multiplying with 1, if so return "Yes". If cell B3 does not contain the string in cell C7 then return "No".
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Check if value contains a text string
We get an error if we try to multiply a text string with a number, we can use that technique to identify numerical values.
B3*1
becomes
23145256*1
and returns 23145256.
Step 2 - Check error
The ISERROR function returns TRUE if argument is an error value and FALSE if not.
ISERROR(B3*1)
becomes
ISERROR(23145256)
and returns FALSE.
Step 3 - Evaluate IF function
IF(ISERROR(B3*1),"Contains text", "No text")
becomes
IF(FALSE,"Contains text", "No text")
and returns "No text".
19. IF function - Exact match case sensitive
The formula in cell C3 checks if the value in cell B3 is equal to the value in cell C11, upper and lower case letters are also evaluated. If a case sensitive match is found then return "Yes" otherwise return "No".
Formula in cell C3:
Cell reference B3 is a relative cell reference meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Check if value equals text string
The EXACT function checks if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters when evaluating arguments.
EXACT(B3,$C$11)
becomes
EXACT("France","FrancE")
and returns FALSE.
Step 2 - Evaluate IF function
IF(EXACT(B3,$C$11), "Yes", "No")
becomes
IF(FALSE, "Yes", "No")
and returns "No" in cell C3.
20. IF function - AND logic
The image above shows a formula in cell D3 that checks if the value in cell B3 is equal to the value in cell C11 and C3 is equal to C12. Both conditions must be met to return "Yes" otherwise return "No".
Formula in cell D3:
Cell reference B3 and C3 are relative cell references meaning they change accordingly when you copy cell D3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Evaluate first logical expression
B3=$C$11
becomes
"Blue"="Green"
and returns FALSE.
Step 2 - Evaluate second logical expression
C3=$C$12
becomes
5=8
and returns FALSE.
Step 3 - Evaluate AND function
The AND function returns TRUE if all arguments return TRUE.
AND(B3=$C$11,C3=$C$12)
becomes
AND(FALSE, FALSE)
and returns FALSE.
Step 4 - Evaluate IF function
IF(AND(B3=$C$11,C3=$C$12),"Yes","No")
becomes
IF(FALSE,"Yes","No")
and returns "No" in cell D3.
21. How to use the IF function between two worksheets?
The image above shows a formula in cell C3 that checks if the value in cell B3 is equal to the value in cell B3 in worksheet 'AND logic'. Note that single quotation marks are used if the worksheet name contains a space character.
Formula in cell D3:
Cell reference B3 is a relative cell references meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Evaluate logical expression
'AND logic'!B3 is a cell reference pointing to cell B3 in worksheet 'AND logic'.
B3='AND logic'!B3
becomes
"Blue"="Blue"
and returns TRUE.
Step 2 - Evaluate IF function
IF(B3='AND logic'!B3, "Yes", "No")
becomes
IF(TRUE, "Yes", "No")
and returns "Yes" in cell D3.
22. IF function - copy cell value?
The image above shows a formula in cell D3 that copies cell B3 if cell C3 is equal to "Yes". Nothing is returned if not equal to "Yes".
Formula in cell D3:
Cell reference B3 and C3 are relative cell references meaning they change accordingly when you copy cell D3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Evaluate logical expression
C3="Yes"
becomes
"Yes"="Yes"
and returns TRUE.
Step 2 - Evaluate IF function
IF(C3="Yes",B3,"")
becomes
IF(TRUE, "A", "")
and returns "A" in cell D3.
23. IF function - add value?
The image above shows a formula in cell C3 that adds the value in cell C7 to cell B3 if cell B3 is equal to 5. Nothing "" is returned if not equal to 5.
Formula in cell D3:
Cell reference B3 is a relative cell references meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Evaluate logical expression
B3=5
becomes
4=5
and returns FALSE.
Step 2 - Evaluate IF function
IF(B3=5,B3+$C$7,"")
becomes
IF(FALSE,B3+$C$7,"")
becomes
IF(FALSE,4+5,"")
becomes
IF(FALSE,9,"")
and returns "" (nothing).
Check out the running total here: How to create a running total (SUM function)?
24. IF function - keep value?
The image above shows a formula in cell C3 that keeps the value in cell B3 if the value is equal to a condition.
Formula in cell D3:
Cell reference B3 is a relative cell references meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Evaluate logical expression
B3=5
becomes
4=5
and returns FALSE.
Step 2 - Evaluate IF function
IF(B3=5,B3,"")
becomes
IF(FALSE,4,"")
and returns "" (nothing) in cell C3.
25. IF function - keep blank?
The image above shows a formula in cell C3 that keeps the blank, if no blank the value in cell C7 is added to the value in column B.
Formula in cell D3:
Cell reference B3 is a relative cell references meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Evaluate logical expression
B3=""
becomes
4=""
and returns FALSE.
Step 2 - Evaluate IF function
IF(B3="","", B3+$C$7)
becomes
IF(FALSE,"", 4+5)
becomes
IF(FALSE,9,"")
and returns 9 in cell C3.
26. If function - based on cell color?
The image above shows a formula in cell C3 that checks the cell background color and if color is equal to yellow add value in cell C7 to cell B3 and return the sum to cell C3..
You must first create a named range in order to use the GET.CELL function. Here is how:
- Go to tab "Formulas" on the ribbon.
- Press with left mouse button on "Name Manager" button.
- A dialog box appears. Press with left mouse button on "New..." button.
- Another dialog box appears.
- Enter a name, I chose Color.
- Type the following formula in "Refers to:"
=GET.CELL(63,INDIRECT("rc",FALSE))
- Press with left mouse button on the OK button.
- Press with left mouse button on the "Close" button.
Now back to the worksheet, formula in cell C3:
Cell reference B3 is a relative cell references meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Note, the GET.CELL function has some serious flaws:
- Obsolete and unsupported.
- Microsoft may remove the GET.CELL function whenever they feel like it.
- It does not update when a cell background-color is changed, you need to press F9 to recalculate the formulas.
- It does not support all the millions of colors available in Excel, only around 60 colors.
I recommend that you use Conditional Formatting to color cells based on a condition and use the same condition in the IF function.
Here is an example: How to sum by color?
Explaining formula in cell C3
Step 1 - Identify cell background-color
Color=6
becomes
6=6
and returns TRUE. A yellow background-color returns 6.
Step 2 - Evaluate IF function
IF(Color=6,B3+$C$7,B3)
becomes
IF(TRUE, 4+5,4)
and returns 9. 4+5 = 9.
27. How to work with multiple values in the IF function? (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 on 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 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 yourself, they appear automatically. Note that Office 365 subscribers do not need to enter this as an array formula, simply enter it as a regular formula. This is a new feature in Excel called Dynamic arrays.
Explaining formula in cell F5
Step 1 - Logical expression
B3:B10=F3
becomes
{2012; 2011; 2012; 2012; 2011; 2012; 2012; 2011}=2012
and returns
{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Step 2 - Evaluate IF function
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({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; ""}.
Step 3 - MEDIAN function
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.
28. IF function - between two numbers?
The image above shows a formula in cell D3 that checks if number in cell B3 is between two numbers specified in cell C8 and C9. If not in range then return nothing "".
Formula in cell D3:
Cell reference B3 is a relative cell references meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Explaining formula in cell C3
Step 1 - Evaluate first logical expression
B3>=$C$8
becomes
4>=5
and returns FALSE.
Step 2 - Evaluate second logical expression
B3<=$C$9
becomes
4<=6
and returns TRUE.
Step 3 - Multiply logical expressions (AND logic)
The parentheses are needed to control the order of operation. We need to compare values first before multiplying boolean values.
(B3>=$C$8)*(B3<=$C$9)
becomes
FALSE*TRUE
and returns 0 (zero) which is equivalent to FALSE.
Step 4 - Evaluate IF function
IF((B3>=$C$8)*(B3<=$C$9), C3, "")
becomes
IF(0, C3, "")
and returns "" (nothing) in cell D3.
29. IF function - based on date?
The image above shows a formula in cell D3 that checks if the date in cell B3 is equal to the date in cell C8, if so return the number in cell C3.. If not then return nothing "".
Formula in cell D3:
Cell reference B3 is a relative cell references meaning it changes accordingly when you copy cell C3 and pastes it to cells below.
Note, keep in mind that dates in Excel are actually numbers formatted as dates. This makes it possible to add or subtract days, months, years, hours, and seconds to a date.
For example, number 1 is 1/1/1900 and 1/1/2000 is 36526. You can see that this is correct, type 1 in a cell. Select the cell, press CTRL + 1 to format the cell. Press with left mouse button on Date and then press OK button.
Explaining formula in cell D3
Step 1 - Compare dates
B3=$C$8
becomes
44872=44874
and returns FALSE.
Step 2 - Evaluate IF function
IF(B3=$C$8,C3,"")
becomes
IF(FALSE,44872,"")
and returns "" (nothing) in cell D3.
'IF' function examples
The following 266 articles have formulas containing the IF function.
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This post explains how to lookup a value and return multiple values. No array formula required.
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]
The array formula in cell D3 calculates an average and ignores 0 (zeros). =AVERAGE(IF(B3:B8<>0,B3:B8,"")) The formula above is an array […]
Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]
The AVERAGE function ignores empty cells, text values, and boolean values automatically, however, it doesn't handle error values. The AVERAGE […]
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
Question: List of data and blank cells in a column which will be added from day to day. There are […]
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Add cell values to a single cell with a condition, no VBA in this article.
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]
The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Question: How do I count how many times a word exists in a range of cells? It does not have […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
The image above shows a formula in cell C2 that searches for a value based on two conditions specified in […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]
Lookup with criteria and return records.
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]
This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]
The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]
The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]
I this article I will show you how to get numerical values from a cell range manually and using an […]
Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Table of Contents Extract shared values between two columns Extract shared values between two columns - Excel 365 Extract shared […]
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]
This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]
The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]
Functions in 'Logical' category
The IF function function is one of many functions in the 'Logical' category.
14 Responses to “How to use the IF function”
Leave a Reply to How to use the IFERROR function
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.
[...] 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 […]
The IF function is nicely explained. The examples used are varied covering different types of arguments and situations.