## IF function explained

** Table of Contents**

- Overview
- Example 1 - Logical test
- Example 2 - Value if TRUE/FALSE
- Example 3 - Nested IFs
- Example 4 - Many conditions
- Example 5 - Array formula
- Download excel *.xlsx file

### Overview

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

### Example 1 - Logical test

Any value or expression that returns TRUE or FALSE.

- Ex 1, A1=5. If cell A1 is 5 the expression returns TRUE
- Ex 2, You can also compare a cell value to a text string: A1="Car".
- If a number is returned: 0 (zero) is FALSE and all values above 0 (zero) returns TRUE

### Example 2 - Value if TRUE/FALSE

The second argument (value_if_true) in the IF function is a value that is returned if the logical test returns TRUE. The third argument (value_if_false) in IF function is a value that is returned if the logical test returns FALSE.

The IF function can return a boolean value, number or a text string.

### Example 3 - Nested IFs

You can nest IF functions up to 64 times (excel 2007 and above) but that is probably a bad idea. Example 4 shows you how to use many conditions without nesting.

**Formula in cell E13:**

**Explaining formula**

=IF(B13<3,"A",IF(B13>=6,"C","B"))

becomes

=IF(4<3,"A",IF(B13>=6,"C","B"))

becomes

=IF(FALSE,"A",IF(B13>=6,"C","B"))

becomes

=IF(FALSE,"A",IF(4>=6,"C","B"))

becomes

=IF(FALSE,"A",IF(FALSE,"C","**B**"))

### Example 4 - Many conditions

**Formula in cell F3:**

The VLOOKUP function returns the closest value less or equal to the lookup_value.

### Example 5 - Array formula

**Array formula in cell H5:**

**How to enter an array formula**

- Select cell H5
- Paste array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys

**Explaining array formula in cell H5**

MIN(IF(B3:B19=F3,C3:C19,""))

becomes

MIN(IF({**2012**; 2011; **2012**; **2012**; 2011; **2012**; **2012**; 2011; **2012**; 2011; 2011; 2011; **2012**; **2012**; 2011; **2012**; **2012**}=**2012**,C3:C19,""))

becomes

=MIN(IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE},C3:C19,""))

becomes

=MIN(IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE},{3; 76; 56; 51; 12; 6; 13; 36; 36; 85; 12; 71; 73; 66; 35; 98; 20},""))

becomes

MIN({**3**;"";56;51;"";6;13;"";36;"";"";"";73;66;"";98;20})

### Download excel *.xlsx file

### Category: Functions

Comments(14) Filed in category: Excel, Functions, Index

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.Comments(12) Filed in category: Excel, Functions

Comments(12) Filed in category: Excel, Functions

Excel RANDBETWEEN and RAND functions – How to generate random numbers and text

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]Comments(10) Filed in category: Excel, Functions

Learn how the MOD function works

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]Comments(8) Filed in category: Excel, Functions, Mod, Quotient

Comments(5) Filed in category: Excel, Functions

Comments(4) Filed in category: Excel, Functions, Transpose

Comments(4) Filed in category: Excel, Functions, Textjoin

MMULT function – Matrix multiplication

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]Comments(4) Filed in category: Excel, Functions, Mmult

### 9 Responses to “IF function explained”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] 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. […]