IF function in Google Sheets can be used when you want to check a condition and then based on it, returns a specified value if it’s TRUE, or else return the other specified value.
For example, suppose you are evaluating marks obtained by students in an exam and you want to know if the student has passed or not. In this case, you can use the IF function and if the score is greater than 35, it would return “Pass” else it would return “Fail”.
Syntax of IF function in Google Sheets:
IF(logical_expression, value_if_true, value_if_false)
- logical_expression – This is the condition that you check in the function. It is an expression or reference to a cell containing an expression that would return a logical value, i.e. TRUE or FALSE.
- value_if_true – The value the function returns if logical_expression is TRUE.
- value_if_false – (optional argument) The value the function returns if logical_expression is FALSE. If you don’t specify the value_if_false argument and the checked condition is not met, the function would return FALSE.
Now let’s look at some examples to understand how to use the IF function in Google Sheets in real-life scenarios.
Example 1: Checking a Single Condition With If Function
Suppose you have the marks of students in an exam and you want to specify whether a student has passed or failed (as shown below).
You can easily find this out by using the IF function in Google Sheets.
Here is the formula that will do this:
=IF(B2>35,"Pass","Fail")
It checks whether the score is more than 35 or not. If it is, then it returns Pass, else it returns Fail.
Example 2: Checking Multiple Conditions With If Function
Using the same example as above, what if you have a grading system in school and you have to assign a grade to a student based on the marks obtained.
For example, a student gets an F grade if he/she gets less than 35, D for marks between 35 and 50, C for marks between 50 and 70, B for marks between 70 and 90, and A above 90 (as shown below):
In this case, you not only need to check whether the marks are above 35 or not, but also check the range in which it lies.
Here is the formula that will give you the result in this case:
=IF(B2<35,"F",If(B2<50,"D",If(B2<70,"C",If(B2<90,"B","A"))))
This formula first checks if the score is less than 35 or not. If it is, then it returns F, else it moves on to the next condition, and so on.
Example 3: Calculating Commissions Using IF Function in Google Sheets
Google Sheets If Function allows you to perform calculations in the value section.
A good example of this is calculating the sales commission for sales rep using the IF function.
In the example below, a sales rep gets no commission if the sales are less than 50K, gets a 4% commission if the sales are between 50-80K and 10% commission if the sales are more than 80K.
Here is the formula used:
=IF(B2<50,0,IF(B2<80,B2*4%,B2*10%))
In the formula used in the example above, the calculation is done within the IF function itself. When the sales value is between 50-100K, it returns B2*4%, which is the 4% commission based on the sales value.
Example 4: Using AND/OR Operators in IF Function
Within the IF function, you can use the AND/OR operators to check multiple conditions at a time.
For example, suppose you have a list of students with their marks and attendance. Only those students get the scholarship who have a score of greater than 80 and attendance of more than 80%. You can quickly identify such students using the AND condition within the IF function.
Here is the formula that will identify the students eligible for the scholarship.
=IF(AND(B2>80,C2>80%),"Yes",”No”)
It works by checking both the conditions in the AND function. If the AND function returns TRUE, the IF function returns “Yes”, else it returns “No”.
Want to become a Google Sheets expert? We highly recommend checking out Udemy’s Google Sheets Comprehensive Masterclass and the Complete Google Sheets Course.
You May Also like the following Tutorials:
- Google Sheets Help – IF Function.
- How to Sort Data in Google Sheets.
- How to Quickly Split Text in Google Sheets.
- Using COUNTIF Function in Google Sheets.
- Using IFS Function in Google Sheets.
- Using IFERROR function in Google Sheets.
- Using OR function in Google Sheets.
- How to Use SWITCH function in Google Sheets
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
9 thoughts on “Using IF Function in Google Sheets (with Examples)”
I need help!!
I am trying to make a function that says: if A7 = “yes” AND A4<A5 then I want the cell to say "sell shares"
right now I am using: =IF(A7="yes",A4<A5,"sell shares") it is just showing up as "TRUE"
I might be talking crap, but what about this?
=IF(AND(A7=”YES”,D4<E4),"Sell Shares","")
Just be carefull when you drag the formula down, it will also change the A7 to A8, if you don’t want that you should manually change it back to A7
Or add $ to the row number to get it to stay.
Learning something new every day 😀
Hi Jean!
I have a list of all countries in one column, and I have a shorter list showing only countries that are eligible for a specific Shipping service.
Can I create an if function that gives the response to “If this country is part of the dedicated line service, say yes, if not, say no”
I tried but ran into value problems
If this cell (a cell in the ALL country column) shows in this column (list of all countries that are eligible) say YES .. otherwise say NO
Hope that makes sense! 🙂
– Omar
Can you include commands in IF statements?
Good Afternoon
thnx for your great efforts
i need your help in one thing if you please
for example :
i have Cells in column (A) with multiple choices in a drop down list .. on of these choices is ( NOT requested )
i want to make Cells in column (B) becomes black only when i choose the ( NOT requested )
thnx in advance for your help
You can do this with Conditional Formatting ( in the Format Menu). Apply the formatting to Column B and enter a Custom Formula
=$A1 = “NOT Requested”
and set the background for black for the conditional formatting.
Since the text you want to use to trigger the format change is in a different cell, you have to use a Custom Formula. If the text was in Column B, you could just use the Text Equal To condition.
Hope this helps
Comments are closed.