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.
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.
=IF(logical_test, [value_if_true], [value_if_false])
- Arguments 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:
|<>||Not Equal to|
|>=||Greater Than or Equal to|
|<=||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.
➤ In the output Cell E5, we have to type:
➤ 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.
➤ Select Cell E5 and type:
➤ 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.
➤ In Cell D5, the nested IF formula to find the letter grade will be:
➤ 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.
➤ In the output Cell G5, the related formula will be:
➤ 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.
➤ In Cell G5, the related formula with IF and OR functions will be:
➤ 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.
➤ In Cell G5, the related formula will be:
➤ 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.
➤ 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.
➤ Select the output Cell E5 and type:
➤ 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
➤ 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.
➤ In the output Cell E5, the related formula will be:
➤ 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.
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.