Google Sheets IF function - usage and formula examples

The IF function in Google Sheets is one of the easiest functions to learn, and while this holds true, it is also a very helpful one.

In this tutorial, we will take a closer look at how Google Spreadsheet IF function works and what advantages we can get from using it.

What is IF function in Google Sheets?

Whenever we use IF function, we create a decision tree in which certain action follows under one condition, and if that condition is not met - another action follows.

For this purpose, the condition of the function must be in a format of the alternative question with only two possible answers: "yes" and "no".

This is how a decision tree may look like:

Decision tree of the alternative question.

So, IF function allows us to ask a question and indicate two alternative actions depending on the received answer. This question and the alternative actions are known as three arguments of the function.

IF function syntax in Google Sheets

The syntax for the IF function and its arguments are as follows:

=IF(logical_expression, value_if_true, value_if_false)
  • logical_expression - (required) a value or logical expression that is tested to see if it is TRUE or FALSE.
  • value_if_true - (required) the operation that is carried out if the test is TRUE.
  • value_if_false - (optional) the operation that is carried out if the test is FALSE.

Let's explore the arguments of our IF function in more detail.

The first argument represents a logical question. Google Sheets answers this question with either "yes" or "no", i.e. "true" or "false".

How to formulate the question properly, you may wonder? To do that, you can write a logical expression using such helpful symbols as "=", ">", "<", ">=", "<=", "<>". Now we'll try and ask such a question together.

Usage of the IF function

Let's assume that you are working in the company which sells chocolate in several consumer regions and works with many clients.

This is how your sales data look like in Google Sheets:

Sample sales data for Google Sheets.

We need to separate sales which were made in our local regions from those which were made abroad. To accomplish that, we should add another descriptive field for each sale - a country where the sales took place. Since we have lots of data, we want this description field to be created automatically for each entry.

And this is when IF function comes to play. Let's add the "Country" column to the data table. "West" region represents local sales (Our Country), while the rest are the sales from abroad (Rest of the World).

How to write out the function properly?

Place the cursor in F2 to make the cell active and type in the equality sign (=). Google Sheets will immediately understand that we are going to enter a formula. That's why right after we type the letter "i" it will prompt us to choose a function which begins with that same letter. And we choose "IF".

Functions prompts in Google Sheets.

After that, all our actions will be accompanied with prompts as well.

For the first argument of the IF function enter: B2="West". As with the other Google Sheets functions, you don't need to enter the address of the cell manually - mouse click is enough. Then enter comma (,) and specify the second argument.

The second argument is a value that F2 will return if the condition is met. In this case, it will be a text "Our Country".

And again, after the comma write in the value of the 3rd argument. F2 will return this value if the condition is not met: "Rest of the World". Do not forget to finish your formula entry by closing parenthesis ")" and pressing "Enter".

Our function should look like this:

=IF(B2="West","Our Country","Rest of the World")

And F2 returns the text "Our Country".

Google Sheets IF function.

Now, all we have to do is to copy this function down the column F.

Let's examine the other ways of working with IF function.

IF function and text values

The usage of the IF function with a text has already been illustrated in the example above.

Note. Please pay attention that if the text is being used as the argument, then it must be enclosed in double quotes.

IF function and numerical values

We can use numbers for the arguments just as we did it with the text.

However, what is very important here is that the IF function makes it possible not only to fill the cells with certain numbers based on the conditions met, but also to make calculations.

For example, let's say we give our clients the discounts based on the total value of the purchase. If the total is more than 200, then the client receives a 10% discount.

For that, we will use column G and name it "Discount". Then enter IF function in G2, and the second argument will be represented by the formula which calculates the discount:


IF with numbers in Google Sheets.

IF in combination with other functions

As we have already learnt, the text, numbers and formulas can act as the arguments of the IF function. However, other functions can play that role as well. Let's see how it works.

Remember the way we figured out the country where we sold chocolate?

Now we can use OR function as a first argument, which will return "YES" if at least one of the listed conditions is true. We simply change the IF function in the cell F2. For the first argument, we will list all the regions which belong to the sales abroad, and the other sales will automatically become local.

=IF(OR(B2="East",B2="South"),"Rest of the World","Our Country")

IF with OR in Google Sheets.

We can also use the IF function itself as an argument for the bigger IF function.

Let's assume that we have set stricter discount conditions for our clients. If the total purchase is more than 200 units then they get a 10% discount; if the total purchase is between 100 and 199 then the discount is 5%. If the total purchase is lower than 100, there is no discount whatsoever.

The following formula shows how the function will look in the cell G2:


Google Sheets nested IF.

Note that we used IF function as a second argument of the IF function. In this case, the decision tree is as follows:

Nested IF decision tree.

But what if we make it even more fun and complicate the task? Imagine that we're offering the discounted price to the one region only - "East".

To do that correctly, we add the logical expression AND to our function. The formula then will look the following way:


Using AND in Google Sheets nested IF.

As you can see, the number of discounts have reduced greatly while their amount remains intact.

We hope that there's no room for any doubt now that the IF function, though a very simple one at first glance, opens the door to many options to data processing in Google Sheets.

9 Responses to "Google Sheets IF function - usage and formula examples"

  1. Chandra says:

    I have 3 columns with three different values, I want a formula which can say Good or bad based on some If conditions.

    Basically what I want is

    A) The difference in values between Col C and Col A > 12
    B) Value in col C is > 160

    If the above conditions match I want the fourth column to say "Bad" If it fails to match I want it to say "Good"

    This is the formula I have used. =IF(AND(C1-A1 > "12",C1 > "160"),"bad","good") But irrespective I get Good all the time.

    Can you help what is that I am doing wrong here in the formula?

  2. Jerry says:

    Hi I'm trying to get the if statement to do a subtraction but it's coming up with parse error. What I'd like is a time i.e. 07:00 and the if statement subtracts from that by 0:15 if over 4:30, 0:30 if over 6:00 or 0:45 if over 8:00 how would I best wright this ?

  3. Catherine Van Biber says:

    Hi there
    Can you please let me know what the proper equation would be for assigning a specific value to a cell? For example, if a cell is populated (with text, doesn't matter what the text is) and I want to assign the value of 1.25 in the cell directly to the right of the populated cell, how would I type that out in an equation? IF C2 = filled, then 1.25 (or something similar).

    Thank you!

    • Mikael says:

      If I get it right you'd put the formula in D2, where you will populate 1.25, if C2 has a whatever value. This if it can be whatever value, also a number, ecc... you can use the following formula:

      =IF(C2"",1.25,"Field was empty")

      you can replace "Field was empty with whatever you'd wish to do in case C2 was not populated.

  4. Kelly says:

    I am trying to pull in the value of C based on the value of W=A and X=B.

    A1= "John"
    B2 = "3/17/17"
    C3= "20"

    W4 = "John"
    X4 = "3/17/17
    Y4 = "20"

  5. KORI says:

    Help me to make a formula. I have given a date e.g: 1st August 2017. The due date is 7th August 2017. If the completion date is before due, it becomes EARLY. If it completed on the date same as due, it becomes ON-TIME. If it passed the due, it becomes LATE. Thanks in advance.

  6. Marc says:

    In Google Sheets, how can I use an "if" conditional to change the color of a row based on one cell in that row? I am using conditional formatting for one cell (G3:G30), but I want the whole row to be the same color of that the color in the G cell in that row. Is there a certain code to indicate color? I am using 4 colors.

    In Progress=orange

    I know how to copy formatting for font/size, but not for cell color.

  7. Rachel says:

    I am trying to write some if and statements .. So I have a number crossword puzzle, if the students get the write combination I want it to say congrats. I am setting for smaller statements - two cells at a time, if I have to .. But I have something wrong.

    =if(AND($A$3="5",$A$5="1"),"so far so good", " ")

    Any help would be appreciated.

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools -