Easy IF Function Google Sheets Guide (With 8 Examples)

By

Fact Checked By Cindy Wong

if-function-google-sheets The 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. Confused? This in-depth IF function Google Sheets guide will have you mastering this in no time.

The complex 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 it returns the other specified value. Confused? You’re not the only one!

Read this in-depth IF function Google Sheets guide to master the function within minutes. Let’s get started!

What Is an IF Function?

The IF function is a conditional comparison test or function that lets you test whether a condition is true or false. It then returns a value or performs an action based on the result. You can think of an “IF” function in Google Sheets as a decision-maker.

I like to think of it as a comparison tool that lets you compare data against a criteria.

It can be used for tasks like assigning labels or grades, cleaning data, evaluating data errors, organizing data, and more. I’ll show you how to write if statements on Google Sheets for each use in this article.

An Example of an IF Function

If I want to use the IF function sheets to evaluate a specific condition, such as, “Is the light green?” If the condition holds true, it would direct the program down one path (e.g., “Go forward.”).

On the other hand, if the condition is false, it steers the program down a different path (e.g., “Stop and wait.’’).

I use the Google Sheet IF function because it empowers my spreadsheets to adapt their actions based on the circumstances.

IF Function Google Sheets definition

Syntax of the IF Function Google Sheets

To give you a better understanding of the IF Google spreadsheet function, let me guide you through the syntax. The if formula Google Sheets has at least three arguments.

IF(logical_expression, value_if_true, value_if_false)
  • Logical_expression: This is the condition where you check the function. It is an expression or reference to a cell containing an expression that would return a logical value (i.e., TRUE, 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 will return FALSE.

Now that you have a better understanding of the components of the function. I will now walk you through the following examples so you can understand how to use the IF function in Google Sheets in real-life scenarios.

Example 1: Checking a Single Condition With IF Function

Suppose I have a set of students’ exam marks and want to specify whether a student has passed or failed (as shown below):

Single Condition With IF Function

I would use the IF function in Google Sheets and the “pass/fail” formula, as seen below:

=IF(B2>35,"Pass","Fail")
If Function pass fail example

Here’s how to use the IF function in Google Sheets to check a single condition:

Step 1: First, I would select the blank cells to perform the calculation.

Step 2: Then, I would type =IF( into the cell or select it from the formula suggestions.

Step 2: Type =IF( into the cell. You can also select it from the formula suggestions.

Step 3: I select the cell I want to reference, as shown below.

Step 3: Select the cell you want to reference.

Step 4: Here, you can see that I have typed in the logical expression or condition. In this case, it’s >35.

Value IF true using the IF function

Step 5: I then add a comma (,) and type the text I want to return for “value if true” and “value if false.” I finish by enclosing them in quotation marks.

How to close an IF function Google Sheets

Step 6: To finish the equation, I press “Enter.” If the score exceeds 35, it will return a “Pass.” Otherwise, it will return a “Fail.”

Screenshot of the IF function with the Pass or Fail values

Note: The autofill option may appear. If so, you can click the tickbox. This will automatically fill the cells with the same Google Sheets IF formula, saving you time.

Using autofill for IF functions

Example 2: Using IF Functions with “Greater Than”

I can also use the IF function with the “greater than” operator. My example spreadsheet below shows the different values greater than 70 will give students an A grade. Let me guide you through the steps on how to use this function.

Subset information for IF function greater than 70

Step 1: Select an empty cell to perform a different calculation.

Step 2: Type =IF( into the cell or select it from the formula suggestions.

Type =IF( into the spreadsheet cell

Step 3: Select the cell to reference. In this case, it is cell B2.

Select the GSheets cell reference for IF function

Step 4: Type in the logical expression or condition. In this case, I have used <70.

If true, the IF function in Google Sheets is greater than 70

Step 5: Add a comma (,) and type “A” to return an A grade if a student receives marks higher than 70.

Add a comma and type A to return an A grade.

Step 6: To see if the value is false, add a space for the “value if false.” This will leave the rest of the cells blank.

Step 6: You can add an empty space for value if false to leave the rest of the cells blank.

Step 7: Press “Enter” and copy the formula to the rest of the cells.

Pressing Enter to copy if formula to all cells.

Note: As you can see, the formula will return an “A” if a student scores 70 or above. The cell will be left blank if the student scores less than 70.

Short on time but want to learn more about using the IF function? Check out this 2-minute video now!

Example 3: Using IF function with “Equal to”

Another helpful method that I use is the IF function with “Equal to.” This is helpful because it lets you compare a value and what you expect.

Let me explain better using the example spreadsheet below.

As you can see, when the value equals 90, a student will be awarded an A+ grade.

Subset information for IF function greater than 70

I have used the following formula:

 =IF(B2=90,"A+"," ")
We’ll use the formula =IF(B2=90,"A+"," ")

Again, I have used a blank space for value_if_false to leave the rest of the cells blank. This indicates that all students who received a score of 90 will be awarded an A+ grade.

Copy the formula to the other rows

If we copy the formula to the other rows, any score of 90 will be graded A+. In this case, you can also use the Google Sheets IF function with text.

Related reading: How To Sort Data in Google Sheets

Example 4: Using Nested IF Function for Multiple Conditions

In this example, I will check for multiple conditions using the IF function. This works by nesting IF functions inside another IF function.

What is a Nested IF Function?

The nested IF Function is when you have an IF function inside another function. This lets you use multiple conditions with as many IF statements in Google Sheets.

Going back to the example of students’ scores — what if there is a dataset with a grading system and I must assign a grade to a student based on their composite marks?

Scoreboard of student's grades, from A to F

In this case, I need to check whether the marks are above 35 and the range in which it lies. The if formula in Google Sheets that I would use is:

=IF(B2<35,"F",IF(B2<50,"D",IF(B2<70,"C",IF(B2<90,"B","A"))))

This formula first checks whether the score is less than 35. If it is, it will return an F grade. The function will then check the following condition: If the student’s score is between 35-50.

If so, it will award the student a grade of D. The formula will continue to go through the scoring system until all the students have been awarded a grade.

This method is helpful, especially with large data sets, because I only need to input the formula once.

 Example 5: Calculating Commissions Using IF Function in Google Sheets

Another method that I use is combining the Google Sheets: IF function with calculations in the text values section. For example, I might use the IF function to calculate the sales commission for each sales representative, as shown below.

Calculating Commissions Using IF Function

Say that a sales rep gets zero commission for sales under $50,000, a 4% commission if the sales are between $50-80,000, and a 10% commission for sales greater than $80,000.

I would use the following Google Sheets formula for the above example:

=IF(B2<50,0,IF(B2<80,B2*4%,B2*10%))

Let me walk you through the steps to make it easier to understand.

Step 1: Type =IF( into the cell. You can also select it from the formula suggestions.

Step 1: Type =IF( into the cell. You can also select it from the formula suggestions.

Step 2: Type the first condition, which is B2>50. Then, add a 0. This means that if the value is less than 50 — the sales reps will receive zero commission.

Calculating agent commissions using the IF function in Google Sheets

Step 3: Type the second Google Sheets IF statement: B2<80.

Second IF statement for functions sheets

Step 4: Click “Enter” and copy the formula to the rest of the rows.

Copy the IF function to rest of cells

In the example above, the calculation is done within the IF function. When the sales value is between 50-100K, it will return B2*4% (meaning that the 4% commission is based on the sales value).

Example 6: Using IF Function for Blank or Nonblanks

Using ISBLANk with IF

There are certain situations where your data may have some blanks. This can be problematic, especially if you have a large data set. In such cases, you can check if your data has blank cells using the IF function. This is done by combining it with the ISBLANK function.

In the example above, I used the following formula to check if the cells in column A are blank or not:

=IF(ISBLANK(A1),”Blank”,”Not Blank”)

Example 7: Using AND/OR Operators in the IF Function

Within the IF function, you can use the AND/OR comparison operators to check multiple conditions simultaneously.

Suppose you have a list of students’ marks and attendance. Students only receive a scholarship if they score 80+ and have an attendance of more than 80%. You can quickly identify which students will receive a scholarship using the AND condition and nested IFs function.

How to use the IF function with AND in Google Sheets

Here is the formula to identify the students’ scholarship eligibility:

=IF(AND(B2>80,C2>80%),"Yes",”No”)

This formula will check both conditions in the AND function. If the AND function returns TRUE, the IF function will return a “Yes,” indicating this student will receive a scholarship. If the result returns a “No,” the student did not meet the requirements.

Using the IF function with AND to indicate which students will receive a scholarship.

Example 8: Using IF With a COUNT Function

For this following example, we will pretend the teacher of the class needs more than five students to score 50 marks or above on an exam so they do not “Fail” as a teacher.

Here, I use a nested IF and COUNTIF function to show “Pass” or “Fail” based on the data using the formula:

=IF(COUNTIF(B2:B12,">50")>5,"Pass", "Fail")

The COUNTIF function in the above formula only counts the scores in B2:B12 that are >50. The >5 operator outside of the brackets gets the IF function to check whether more than 5 students meet that requirement.

Student scores in an exam using the nested IF function with COUNTIF

Frequently Asked Questions

How Do You Write an IF Function in Google Sheets?

The syntax for the IF function is:

IF(logical_expression, value_if_true, value_if_false)

Can You Use Multiple IF Statements in Google Sheets?

You can use multiple IF statements in Google Sheets by nesting them inside each other. However, an easier way would be to use the IF function, which allows multiple arguments simultaneously. To use for multiple conditions, use the following formula:

=IF(B2<35,"F",If(B2<50,"D",If(B2<70,"C",If(B2<90,"B","A"))))

Can You Put a Formula in an IF Function?

You can use any formula or logical expression as the “logical expression” argument of the IF function. For example, you could use a formula to calculate a sum or average. You could also include a logical expression that checks whether a cell contains a specific value.

The IF function is combined with other arithmetic formulas like AVERAGEIF or COUNTIF.

What Is an Example of an IF Statement?

An example of an IF statement is a Pass/Fail formula:

=IF(A2>40,"Pass","Fail")

If the value in cell A2 is less than 40, the formula will return the word “Fail.” If it’s more than 40, the formula will return “Pass.”

In the examples above, I’ve shown you several examples of how to write an IF statement in Google Sheets. Please refer back if you need further guidance.

Wrapping Up

Now that you’ve read through my easy IF Function Google Sheets guide, I hope you have a better grasp of this concept! Thinking of improving your Google Sheets expertise even more? There’s never been a better time to get ahead of the curve with Udemy’s range of courses!

Also, be sure to check out the following Google Sheets functions tutorials:

 

Popular Posts

9 thoughts on “Easy IF Function Google Sheets Guide (With 8 Examples)”

  1. 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"

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

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