In Microsoft Excel, the IF function is used to check if a condition is met and then the defined statements will be shown based on the given condition. In this article, you’ll learn how you can use this IF function efficiently with different criteria and by combining it with other functions in Excel.

The above screenshot is an overview of the article which represents an application of the IF function in Excel. You’ll learn more about the dataset as well as the methods to use IF function properly in the following sections of this article.

**Table of Contents**hide

**Download Practice Workbook**

You can download our Excel Workbook that we’ve used to prepare this article.

**Introduction to the IF Function**

**Function Objective:**

**Checks whether a condition is met, and returns one value if TRUE, and another one if FALSE.**

**Syntax:**

**=IF(logical_test, [value_if_true], [value_if_false])**

**Arguments Explanation:**

Argument | Compulsory/Optional | Explanation |
---|---|---|

logical_test |
Compulsory |
Given condition for a cell or a range of cells. |

[value_if_true] |
Optional |
Defined statement if the condition is met. |

[value_if_false] |
Optional |
Defined statement if the condition is not met. |

**Logical Operators:**

Operator | Description |
---|---|

= |
Equal to |

<> |
Not Equal to |

> |
Greater Than |

>= |
Greater Than or Equal to |

< |
Less Than |

<= |
Less Than or Equal to |

**Return Parameter:**

**Logical values- TRUE or FALSE if statements are not defined. If statements are defined, they’ll be shown as return values based on the conditions met or not.**

**8 Suitable Examples of Using IF Function in Excel**

**1. IF Function to Show Statements Based on Logical Test**

In our dataset, there are two columns containing **Sales Target **and** Sales Achieved **for some products. By using the **IF** function, we’ll check and show statements in **Column E** if the achieved sales have met the sales target or not.

**📌**** Steps:**

➤ In the output **Cell E5**, we have to type:

`=IF(D5>=C5,"Achieved","Not Achieved")`

➤ Press **Enter**, use **Fill Handle** to autofill the entire column and you’ll find the statuses of all sales based on the defined criteria and statements.

**2. Using IF Function to Return Calculated Result**

Now we’ll use the IF function to find bonuses for some salespersons based on the status of the sales. If a salesperson meets the target, then his/her bonus of the salary will be calculated by the percentage of sales increase.

**📌**** Steps:**

➤ Select **Cell E5** and type:

`=IF(D5>=C5,$C$15*(D5-C5)/C5,"Not Applicable")`

➤ Press** Enter**, autofill the entire column, and you’re done. You’ll find the bonuses for those who have met the sales target and the formula will return with the statement **“Not Applicable”** if target sales are not achieved.

**3. Use of Nested IF Function in Excel**

The nested IF formula is widely used to determine the grade letter of a student in an exam. In our table, 1st 2 columns consist of subject names and marks out of 100 for each one. **Column F** shows the grading system. We’ll find out the letter grade obtained in each subject in **Column D**.

**📌**** Steps:**

➤ In **Cell D5**, the nested IF formula to find the letter grade will be:

`=IF(C5>=80,"A",IF(C5>=70,"B",IF(C5>=60,"C",IF(C5>=50,"D","F"))))`

➤ Press **Enter,** autofill the rest of the cells and you’ll be shown letter grades for all subjects based on the defined grading system.

**4. IF with AND, OR and NOT Functions**

Let’s get introduced to another new dataset now that combines a range of data for a charity foundation. **Columns C, D, E, and F **consist of the donor names, donation amounts, mediums of donations and donation dates respectively. By using the **IF** function before **AND** function, we can add some statements based on the return types of logical values. For example, we want to see the names only in **Column G** who have donated more than $500 before 1 August 2021.

**📌**** Steps:**

➤ In the output** Cell G5**, the related formula will be:

`=IF(AND(D5>$J$11,F5<$J$12),C5,"")`

➤ Press **Enter**, autofill the entire column, and you’re done. You’ll see all the names of the donors based on the selected criteria.

Now by using **OR** function inside the** IF** function, we’ll find out the names of the donors who have donated through cash or cheque from **Column E**.

**📌**** Steps:**

➤ In **Cell G5**, the related formula with IF and OR functions will be:

`=IF(OR(E5=$J$11,E5=$J$12),C5,"")`

➤ Press **Enter**, autofill the entire column and you’ll get all the donor names based on the selected criteria.

In MS Excel, the **NOT** function is used to convert the logical values- **TRUE and FALSE** into **FALSE and TRUE** respectively. So, by using this function inside the **IF** function, we can find out the donors who have not donated through cash.

**📌**** Steps:**

➤ In **Cell G5**, the related formula will be:

`=IF(NOT(E5=$J$11),C5,"")`

➤ After pressing **Enter** and auto-filling the rest of the cells, you’ll get the expected results at once.

**5. IF with ISBLANK, ISTEXT, ISNUMBER and ISLOGICAL Functions**

In Microsoft Excel, ISBLANK, ISTEXT, ISNUMBER and ISLOGICAL are all logical functions through which we can determine if a cell contains nothing, text string, number string, or a logical value respectively. In the table below, we’ll find out in **Column C** what types of data are lying in **Column B**.

**📌**** Steps:**

➤ In the output **Cell C5**, we have to type:

`=IF(ISTEXT(B5),"Text",IF(ISNUMBER(B5),"Number", IF(ISBLANK(B5),"Blank",IF(ISLOGICAL(B5),"Logical Value",""))))`

➤ Press **Enter**, autofill the entire column and you’ll find out types of all data right away based on the criteria and defined statements.

**6. Error Checking by Combining IF and ISERROR Functions**

**ISERROR** is another logical function that is used to check whether a cell contains an error message. By combining ISERROR with IF function, we can remove the error messages and show the output as blank.In the screenshot below, the values in **Column B** are divided by the values in **Column C** and the quotients are found in **Column D**. Based on different data types, we’ll find some errors in **Column D**. In **Column E**, we’ll use **IF** and** ISERROR** functions to perform the similar calculations but here if an error message appears, then it’ll not be shown, rather a blank cell will be assigned there.

**📌**** Steps:**

➤ Select the output **Cell E5** and type:

`=IF(ISERROR(B5/C5),"",B5/C5)`

➤ Press** Enter,** autofill the entire column with the **Fill Handle**, and the return values will be displayed at once in **Column E**.

**7. IF with Ampersand (&) or CONCATENATE Functions to Join Text and Numerical Values**

In this section, by using **Ampersand(&) **as well as **CONCATENATE** functions, we’ll join the text and number values based on the logical test with the **IF** function. Based on the sales dataset below, we’ll make some statements for those who have achieved the sales target and those who couldn’t

**📌**** Steps:**

➤ In **Cell E5**, the related formula will be:

`=IF(D6>=C6,CONCATENATE("Target Achieved by ",B6, ", Total Sales: ",D6),"Target Not Achieved, "&(C6-D6)&" Sales Short")`

➤ Press **Enter,** autofill the entire column and you’ll be shown the resultant values.

**8. IF with DATE Function in Excel**

We can use IF with a DATE function to determine the due status for tuition fees of the students. Assuming, the deadline for the payment of tuition fees for July is **7/31/2021**. We’ll find out the status of the students who paid the tuition fees in time and who couldn’t.

**📌**** Steps:**

➤ In the output **Cell E5**, the related formula will be:

`=IF(C5<=DATE(2021,7,31),"In Time","Delayed")`

➤ Press **Enter**, fill down the entire column and you’ll get the return values with defined statements at once.

**💡**** Things to Keep in Mind**

🔺 In the arguments of IF function, although the 2nd argument **[value_if_true] **is considered optional, but if you don’t input the statement there, the function will not be executed and a message will pop up and ask if you want to type a formula or make it a text value.

🔺 If you don’t define 2nd and 3rd arguments but just use a **Comma(,)**, then the function will return **0** for any logical test to be found **TRUE or FALSE**.

🔺 IF function is not able to include more than one logic for which you have to use either **nested IF** where multiple conditions can be added. Or you can use the **IFS** function directly where you’ll find options to add multiple conditions.

🔺 If you want to sum based on a condition, then you can use directly **SUMIF** instead of combining IF and SUM functions. Similarly, to count cells based on conditions, it’s better to use **COUNTIF** in lieu of incorporating IF and COUNT functions.

**Concluding Words**

I hope all of the methods mentioned above to use the IF function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.