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, I invite you to take a closer look at how Google Spreadsheet IF function works and what advantages you will get from using it.

What is the IF function in Google Sheets?

Whenever you use the IF function, you 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 what a decision tree may look like: Decision tree of the alternative question.

So, the IF function allows you 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 (or comparison operators) as "=", ">", "<", ">=", "<=", "<>". Let us try and ask such a question together.

Usage of the IF function

Let's assume that you are working in the company selling chocolate in several consumer regions with many clients.

This is what your sales data may look like in Google Sheets: Sample sales data for Google Sheets.

Imagine that you need to separate sales made in your local regions from those from abroad. To accomplish that, you should add another descriptive field for each sale – a country where the sales took place. Since there is a lot of data, you need this description field to be created automatically for each entry.

And this is when the 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 you are going to enter a formula. That's why right after you type the letter "i" it will prompt you to choose a function that begins with that same letter. And you should choose "IF". Function prompt in Google Sheets.

After that, all your actions will be accompanied by 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 – a 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 the text "Our Country".

And again, after the comma, write 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".

Your entire formula should look like this:

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

If everything is correct, F2 will return the text "Our Country": Google Sheets IF function.

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

Tip. There's one way to process the entire column with one formula. The ARRAYFORMULA function will help you do that. Using it in the first cell of the column, you can test all cells below against the same condition, and return the corresponding result to each row at the same time:

=ARRAYFORMULA(IF(B2:B69="West","Our Country","Rest of the World")) Process entire ranges using IF + ArrayFormula.

Let's examine the other ways of working with the 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. If the text is being used as the argument, then it must be enclosed in double-quotes.

IF function and numerical values

You can use numbers for the arguments just as you did with the text.

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

For example, let's say you offer your clients various discounts based on the total value of the purchase. If the total is more than 200, then the client gets a 10% discount.

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

=IF(E2>200,E2*0.1,0) IF with numbers in Google Sheets.

IF blanks/non-blanks

There are cases when your result depends on whether the cell is empty or not. There are two ways to check that:

  1. Use the ISBLANK function.

    For example, the following formula checks if cells in column E are empty. If so, no discount should be applied, otherwise, it's 5% off:

    =IF(ISBLANK(E2)=TRUE,0,0.05) Check if a cell is blank in Google Sheets.

    Note. If there's a zero-length string in a cell (returned by some formula), the ISBLANK function will result in FALSE.

    Here is another formula to check if E2 is empty:

    =IF(ISBLANK(E2)2<>FALSE,0,0.05)

    You can turn the formula the other way around and see if cells are not blank instead:

    =IF(ISBLANK(E2)=FALSE,0.05,0

    =IF(ISBLANK(E2)<>TRUE,0.05,0)

  2. Use standard comparison operators with a pair of double-quotes:

    Note. This method considers zero-length strings (indicated by double-quotes) as empty cells.

    =IF(E2="",0,0.05) – check if E2 is blank

    =IF(E2<>"",0,0.05) – check if E2 is not empty.

    Tip. In a similar manner, use double-quotes as an argument to return an empty cell by the formula:

    =IF(E2>200,E2*0,"")

IF in combination with other functions

As you have already learned, 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.

Google Sheets IF OR

Remember the first way you figured out the country where you sold chocolate? You checked if B2 contained "West".

However, you can build the logic the other way around: list all the possible regions that belong to the "Rest of the World" and check if at least one of them appears in the cell. The OR function in the first argument will help you do that:

=OR(logical_expression1, [logical_expression2, ...])
  • logical_expression1 – (required) the first logical value to check for.
  • logical_expression2 – (optional) the next logical value to check for.
  • and so on.

As you can see, you just enter as many logical expressions as you need to check and the function searches if one of them is true.

To apply this knowledge to the table with sales, mention all the regions that 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.

Google Sheets IF AND

The AND function is just as simple. The only difference is that it checks if all listed logical expressions are true:

=AND(logical_expression1, [logical_expression2, ...])

E.g. you need to narrow the search to your town and you know that it is currently buying only hazelnuts. So there are two conditions to consider: region – "West" and product – "Chocolate Hazelnut":

=IF(AND(B2="West",C2="Chocolate Hazelnut"),"Our Country","Rest of the World") Google Sheets IF AND formula.

Nested IF formula vs. IFS function for Google Sheets

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

Let's assume that you have set stricter discount conditions for your clients. If the total purchase is more than 200 units, they get a 10% discount; if the total purchase is between 100 and 199, 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:

=IF(E2>200,E2*0.1,IF(E2>100,E2*0.05,0)) Google Sheets nested IF.

Note that it is another IF function that is used as the second argument. In such cases, the decision tree is as follows: Nested IF decision tree.

Let's make it even more fun and complicate the task. Imagine that you're offering the discounted price to the one region only - "East".

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

=IF(AND(B2="East",E2>200),E2*0.1,IF(AND(B2="East",E2>100),E2*0.05,0)) Using AND in Google Sheets nested IF.

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

There's also an easier way to write the above thanks to the IFS function:

=IFS(condition1, value1, [condition2, value2, …])
  • condition1 – (required) is the logical expression you want to test.
  • value1 – (required) is the value to return if the condition1 is true.
  • and then you just list conditions with their values to return if they are true.

Here's how the above formula will look with IFS:

=IFS(AND(B2="East",E2>200),E2*0.1,AND(B2="East",E2>100),E2*0.05) Use IFS instead of nested IF.

Tip. If there's no true condition, the formula will return the #N/A error. To avoid that, wrap your formula with IFERROR:

=IFERROR(IFS(AND(B2="East",E2>200),E2*0.1,AND(B2="East",E2>100),E2*0.05),0) Avoid errors with IFERROR.

SWITCH as an alternative to multiple IFs

There's one more function you may want to consider instead of the nested IF: Google Sheets SWITCH.

It checks if your expression corresponds to a list of cases, one by one. When it does, the function returns a corresponding value.

=SWITCH(expression, case1, value1, [case2, value2, ...], [default])
  • expression is any cell reference, or a range of cells, or even an actual math expression, or even a text that you'd like to equal to your cases (or test against the criteria). Required.
  • case1 is your first criteria to check the expression against. Required.
  • value1 is a record to return if the case1 criterion is the same as your expression. Required.
  • case2, value2 repeat as many times as criteria you have to check and values to return. Optional.
  • default is also completely optional. Use it to see a specific record if none of the cases is met. I'd recommend using it every time to avoid errors when your expression doesn't meet matches among all the cases.

Here are a couple of examples.

To test your cells against a text, use ranges as an expression:

=ARRAYFORMULA(SWITCH(B2:B69,"West","Our Country","Rest of the World")) Use a range as an expression to test against the text.

In this formula, SWITCH checks what record is in every cell in column B. If it's West, the formula says Our Country, otherwise, Rest of the World. ArrayFormula makes it possible to process the entire column at once.

To work with calculations, it's better to use a boolean expression:

=SWITCH(TRUE,$E2>200,$E2*0.1,AND($E2<200,$E2>100),$E2*0.05,0) Use booleans as an expression to test against calculations.

Here SWITCH checks if the result of the equation is TRUE or FALSE. When it's TRUE (like if E2 is really greater than 200), I get a corresponding result. If none of the cases in the list is TRUE (meaning they are FALSE), the formula simply returns 0.

Note. SWITCH doesn't know how to calculate the entire range at once, so no ARRAYFORMULA in this case.

IF statements based on a count

One of the questions we get asked a lot is how to create the IF formula that will return whatever you need if the column contains or doesn't contain a certain record.

For example, check if a customer's name appears more than once in a list (column A) and put the corresponding word (yes/no) into a cell.

A solution is simpler than you may think. You need to introduce the COUNTIF function to your IF:

=IF(COUNTIF($A$2:$A$69,$A2)>1,"yes","no") Use COUNTIF within your IF function.

Make Google Sheets build IF formulas for you – IF Formula Builder add-on

If you're tired of keeping track of all those extra characters and proper syntax in formulas, there's another solution available.

IF Formula Builder add-on for Google Sheets offers a visual way of creating IF statements. The tool will handle syntax, extra functions and all required characters for you.

All you need to do is:

  • fill blanks with your records one by one. No special treatment for dates, time, etc. Enter them as you always do and the add-on will recognize the data type.
  • select required comparison operators from the suggested drop-down lists.
  • if needed, add multiple logical expressions in a click: IF OR, IF AND, ELSE IF, THEN IF.
IF Formula Builder add-on for Google Sheets.

As you can see, each logical expression takes its own line. The same goes for true/false outcomes. This reduces the number of possible confusion over the formula drastically.

As you fill everything out, the formula for use will grow in the preview area at the top of the window. To its left, you can select a cell in your sheet where you'd like to have the formula.

When you're ready, paste the formula into the cell of interest by clicking the Insert formula button at the bottom.

Please visit the online tutorial for IF Formula Builder to see all options described in detail.

I 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 for data processing in Google Sheets. But if you still have questions, feel free to ask them in the comments section down below – we'll be happy to help!

You may also be interested in

Table of contents

607 comments

  1. DROP BOX = MALAYSIA,SINGAPORE,FILIPINE
    how.

    select malaysia = 17% /
    select singapore = 16%

    please help

  2. Hi, can you help me how to make a formula that based on the covered period time it will call the window:

    1st window
    4-8PM
    2nd window
    8-12MN
    3rd window
    12MN-4AM
    4th window
    4-8AM
    5th window
    08-12NN
    6th window
    12-4PM

    Thanks.

  3. HELLO,

    PLEASE HELP

    IF A CELL CONTAINS 50% OR ABOVE THEN CLASS 1 OR BELOW 50% THEN ECE.
    FORMULA IN CELL A2
    CONDITION IN CELL A1

  4. Hello,

    Can you let me know where i am going wrong. it says i have only imputed 1 argument.

    =IF(OR(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EjfESGzrXaeh6oaWrZWMGOuCEzgdgFQpO6pzEMZxpJc/edit#gid=1328129828&range=J5:J66", "bennys tune sheet.!J5:j66")="YES",(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EjfESGzrXaeh6oaWrZWMGOuCEzgdgFQpO6pzEMZxpJc/edit#gid=1328129828&range=J5:J66", "bennys tune sheet.!J5:j66")="NO"),"YES", "NO"))

    Many thanks in advance, and thank you all for the amazing guide :D

    • Hello Alex,

      There are a couple of excess brackets. Here's a correct formula:
      =IF(OR(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EjfESGzrXaeh6oaWrZWMGOuCEzgdgFQpO6pzEMZxpJc/edit#gid=1328129828&range=J5:J66", "bennys tune sheet.!J5:j66")="YES",IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EjfESGzrXaeh6oaWrZWMGOuCEzgdgFQpO6pzEMZxpJc/edit#gid=1328129828&range=J5:J66", "bennys tune sheet.!J5:j66")="NO"),"YES","NO")

      BTW, have you seen our IF Formula Builder from the last part of the article? It worries about the correct syntax for you so I'd suggest you check it out :)

  5. Hi! and thanks for a great article, very helpful. I am hoping you can help me with what is possibly a very basic function - I want to create a signal between the change of two numbers. For example, if on Tuesday the number in cell A1 is 10 and on Wednesday the number in cell A2 is 5, I would like a trigger cell to tell me that there has been 50% change from one day to the next.

    Can you please help me with this? Thanks so much!

    Patrick

  6. Hi I am looking at using the IFs function however, I want it to return a cell from another sheet or be able to return a true statement that has skipped lines if you know what I mean IE

    in A1 = Apples and I want my True statement to appear as:
    B1 Red
    Apple
    Sauce
    Rocks

    instead of Red Apple Sauce Rocks?

    • Hi Sarah,

      you can either press Alt+Enter when you type the formula to wrap the words,
      or insert line breaks using a true statement like this: "Red"&CHAR(10)&"Apple"&CHAR(10)&"Sause"&CHAR(10)&"Rocks"

  7. Related text need to display in 1st table from the 2nd table, If we write a remarks in the 2nd table following and related text in the 1st table -- can any one write the if formula for this

    • Hello Suresh,

      I'm afraid there's not enough info to provide you with a formula. Please describe your task in detail so we could help.

  8. DOH logic flow

    5 & 7 & 10 day- Priority P4

    How can I run this logic in google sheet? Please help on urgent note.

  9. I am using a simple IF statement to populate a numerical value. I then use that value for another calculation and a SUM at the bottom the section.

    It's not reading the number as a number in the SUM function, but it is in the other cell.

    I've also tired saving and opening in Excel.

    I'd so appreciate suggestions, it's making me crazy LOL.

    Analyze request 2 1
    Is the data in Personas? Yes =IF(H4 = "yes", "2", "8") =I4/3
    Is the destination in Self Service? No =IF(H5 = "yes", "0", "8") =I5/3
    Requirements LOE Estimate =SUM(I3:I5) =SUM(J3:J5)

    Analyze request 2 1
    Is the data in Personas? Yes 2 1
    Is the destination in Self Service? No 8 3
    Requirements LOE Estimate 2 4

    • Hello Rebecca,

      When you put numbers in the IF formula in double quotes, the formula returns them as text because this is how text is written in formulas. To have numbers as numbers, remove those double quotes from around your numerics, like this:
      =IF(H4="yes", 2, 8)

  10. how would i do an if statement like "if there is nothing in the cell above, move everything to cell above?" Is it possible to do that?

    • Hello Eletron,

      I'm afraid you won't be able to do that with formulas. You see, formulas return results into cells where they are entered. Also, you can't enter the formula into a cell and check that cell for any condition at the same time. In other words, if you enter the formula to B2 and check if B1 is empty, the result will be returned to B2.
      What's more, you cannot cut-paste data using formulas, only copy-paste it. So any data joined from other cells in B2 will also remain in all other cells.

      • oh ok

  11. Is there a function that would be true if the cell of interest contains any of the words in a specified column? I was going to use an IFS statement to tag rows based on what they contain.

  12. I'm trying to have a cell automatically round up no matter if the value is less than .5 or not. I need the value to round up always. So for instance if the value is 2.35, I need it to round up to 3.
    Thank you!

  13. So I'm trying to use a pretty basic IF OR formula in Google Sheets: =IF(OR(I299),"I2","").

    The trick is that for my value if true, I want to use a cell reference, not just a static value / word. When I use just the IF statement without OR, it accepts I2 as a cell reference, but when I make it an IF OR it no longer recognized my "value if true" as a cell reference.

    Is there anyway to adjust that?

    • Hello Natalie,

      If you want I2 to be treated as a cell reference, you should omit double-quotes. Double-quotes make values text strings.
      Also, OR requires some condition, not just a cell reference, and is usually used to list several conditions to see if at least one of them is true. Hence, the correct formula for you should look like this:
      =IF(I299=SOMETHING,I2,"")

      If that doesn't really work for you, please describe your task in detail and I'll help with the whole formula.

  14. if the two column cells are red in colour then write 0.5 in another column how to write a formula can you please tell any one

    • Hello Sethu,

      There are no standard formulas that would check cell colors. We have a special tool for that: Function by color. You can use it to count colored cells. However, it can’t return the value you need based on colors. You will need to build the simplest IF formula afterwards to return 0.5 if our add-on returns 2.

  15. Hi everyone,

    I've read thru many comments but still not understanding exactly how to approach my specific calculation need.

    My need is specific to the Canadian Recovery Benefit, which has a cap of 18K for my situation. I am allowed to make up to 38K in salary/profit without penalty or obligation to reimburse any portion of the 18K. But for every dollar made over 38K that I make (with the exlusion of the 18K govt benefit) I would need to reimburse half of the benefit received (0.50) until I reach the threshold where I am obligated to reimburse all of it (which if my calc is correct, is 74K total salary).

    ie. If I make 38K or less, and also receive the 18K govt benefit, I am not obligated to reimburse any of the benefit. I am naturally obligated to pay the income tax on the 56K total.

    If I make 48K (exluding the govt benefit), I am obligated to reimburse 5K of the 18K govt benefit received. 0.50 cents per every dollar of the 10K I am over the limit.

    If I make 74K (excluding the govt benefit), I am obligated to reimburse the full 18K govt benefit. (74K being the threshold of 38K, plus 2X the 18K maximum benefit)...

    I'm trying to generate a sheet in which I can enter the amount of money I have made to date into one cell, and have the sheet tell me 1) If I am over the limit - this calc is easy, and, 2) tell me how much I need to reimburse in the even that I make of over the 38K.

    I am assuing this is not that hard, but the formulas for it are above me.

    Here's as far as I got:
    =IF(I41=">$38,000.00","No Pay", "Pay") this would simply alert me if I had crossed the threshold and needed to pay. Its not very elegant, but for the first part seems to work.

    But what formula (or addition to the original) would tell me how much of cell I41 would be payable if it crossed the 38K...? Iaking into account the .50 of every 1.00 more made... I hope this makese sense.

    Any help or tips to making this work are greatly appreciated. Thank you!
    Bob

  16. Hi

    I am trying to populate a yearly expense dashboard I created. I have sheets for every month. In column B (expense category "Grocery") and column D ($ amount)

    I am trying to add all values in Column D ($ amount) that have "Grocery "in column B to populate a monthly total for groceries on my dashboard.

    I am tired of sorting the column by category and redoing the formula to calculate totals every few week.

    Is this possible?

    • Hi Natan,

      I'm sorry I'm afraid I need more details on your task to be able to suggest anything.

      Please create a small sample spreadsheet with (1) a couple of sheets with the example data and (2) with a sheet showing the result you want to get. Then share this file with us: support@apps4gs.com. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.
      I'll look into it and see what may help.

      • Hi I have sent this.

        • Hi Natan,

          Thank you for sharing a sample. You need to use the SUMIF function for the task. I entered the correct formulas to the first 3 columns on the Dashboard sheet so you could see what to do. If you need more details on the SUMIF function, we have a cool blog post about it, feel free to look through :)

  17. I am working with schedules, for Example: 0300-1100,1100-1900,1900-0300 three different shifts, How can I make all 0300-1100 scheduled employee names appear in a group?

    Thank you

    • Hello Jake,

      Please create a small sample spreadsheet with 2 sheets: (1) example data, (2) the result you want to get, and share it with us: support@apps4gs.com. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.
      I'll look into it and try to come up with a solution.

      • Hi Natalia,
        I just shared the spreadsheet to that email. Thank you.

        • Hi Jake,

          I've got the file, thank you.
          Please look at B16, B19, D16 and D19. I used QUERY to return the required records. You just need to create similar formulas for other days.
          The same can be done (with and without formulas) with our Multiple VLOOKUP Matches add-on.

          Also, B3:I12 contained some excess spaces that prevented from getting the correct results. I trimmed them with our Remove spaces tool.

          Hope this helps!

  18. Want to set True if cell contains a specific word. How to do that?
    for ex: Let's say cell F4="Rest of the world". Then, if "Rest" contains in the cell F4, set True, else False.

  19. I am trying to create a IF formula where IF cell P2 = Y then "Need to be invoiced" is entered into Q2, however, if R2 has a date inputted into it, Q2 will have "Invoiced" into it. I am having trouble as I get one or the other to work but not together.

      • That formula worked with the exception when P2 is yes but there is no date in R2, the Need to be invoiced did not populate. do I need either an OR or And function in there?

        • Nevermind - I figured it out! P2 uses the word "Yes" not just a "Y" as soon as I fixed that it worked.
          Thank you for your help!

  20. Hi, I am looking for a formula to help with our staff holiday tracking sheet. We have a new policy where holiday entitlement run from Birthday to birthday or each staff (we used to run the from 01/01 to 31/12) As it is our first year doing this it will have run from 01/01 until their birthday this year and then start again. I need a formula that will: one, pull through the start date of the holiday year and the end date and two, change date after the birthday has passed this year. I also then need one that will calculate how man holiday days are accrued between the start and end date of the holiday year.
    E2 - Staff name
    E3 - Holiday year start date (Need formula for this)
    E4 - Holiday year end date (Need formula for this)
    E5 - Holiday entitlement (need formula)

    A separate page will have all names and birthdays.
    Sheet name is Bdays - A1 has the bday date in (12/02/2021)

    Any help would be much appreciated. Im unsure if i need a Vlookup, or IF or maybe both?

    • Hi Michaela,

      Do you intend to use a drop-down list of names in E2 and want other cells to change accordingly or you'd prefer having each staff member's info displayed at the same time?

      For me to understand your task better, please share a small sample spreadsheet with us (support@apps4gs.com) with an example of your data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
      I'll look into it and try to come up with a solution.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)