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

### 8 Responses to “IF function explained”

### Leave a Reply

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

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

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