How to Use IF Function in Excel (8 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

if cfunction overview 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.


Introduction to the IF Function

if function syntax in excel

  • 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.


How to Use IF Function in Excel: 8 Suitable Examples

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.

if function to show statements based on logical test

📌 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.

if function to show statements based on logical test


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.

if function with calculation in excel

Read More: How to Use MAX IF Function in Excel 


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.

nested if function in excel

Read More: Nested IF and AND Functions in Excel


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 the 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.

if with and or not functions in excel

Now by using the 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.

if with and or not functions in excel

In MS Excel, the 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.

if with and or not functions in excel

Read More: How to Check If a Value Is Between Two Numbers in Excel


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.

if with isnumber islogical isblank istext in excel

Read More: How to Use Multiple IF Statements with Text in Excel 


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.

if function with iserror in excel


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

In this section, by using Ampersand(&) as well as the CONCATENATE function, 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.

if function with concatenate and ampersand in excel


8. IF with DATE Function in Excel

We can use IF with a the 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.

if with date function in excel

Read More: How to Use IF Formula with Dates


💡 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.


Download Practice Workbook

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


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.


Excel IF Function: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo