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. i want to permit serially typing to 50 staff members in excel(google spread sheer) eg. 1.ABC, 2.DEF, 3.GHI, 4.JKL, in this serial other than 1 will not type data before 1st NO., then only No. 2 will type data, then only 3, then only 4 ---will type the data by using e-mail

    • Hello there,

      I'm really sorry but your task is not clear to me. Please describe it in more detail and provide an example of what you have at the start and what you're trying to get.
      I'll try my best to help.

  2. Principal:Interest:Balance:
    $330.29 $69.71 $23,465.76
    $83.32 $131.68 $23,382.44
    $587.18 $27.82 $22,795.26
    $606.29 $8.71 $22,188.97
    $536.55 $78.45 $21,652.42
    $0.00 $21,652.42
    I'm wanting the spreadsheet to show "$0.00" if there is no principal or interest is added in the row. How would I do the formula to make it show this? =SUM(K10-I11) is what the formula is looking like!

    • Hello Mike,

      I'm sorry I'm a bit confused. Is it the balance that should show $0.00 if principal and interest are missing or something else? What do you use the SUM formula for?
      Please provide more details on your task so I could understand it fully and suggest accordingly.

      Thank you.

  3. I pickup cars in different zones and each zone has a different pay rate. Is there an if formula to calculate each zone and pay rate on one sheet?

    • Hello Scott,

      You can either build a nested IF and specify each zone and its pay rate there (they can be listed in an additional table and simply referenced in the formula) or use IFS instead.

  4. how can I input the value from the cell in a formula? for instance
    =If($B2="Campbell", substitute($H2," [ ", " Campbell "),$H2)
    The value of B2 is Campbell but the value of C2 is another name. So rather than have to type the name manually or pasting it into the formula, how can I input the value from any cell into the formula above?

    • Hello JB,

      I'm sorry, can you please provide examples of what is exactly in C2 (since you're looking for a square bracket) and what you need to have as a result of the formula?

  5. Hi,
    i wanted to make due date format based on three criteria. I wanted a texts on a specific cell like "Processing" if date on other cell is later than 7 days from today, "Coming soon" if date is within next 7 days from today and "Pending" is date is earlier than today. i used below formula but only for "coming son " criteria not working properly. cell shows "coming soon" if the date is one day earlier than today. others criteria working properly.

    Formula used:
    =IFS(I2<TODAY(),"Pending",I2TODAY()+7,"Processing")

    Please help me on that.

    • Hi Munna,

      You need to use AND for the "Coming soon" criteria:
      AND(I2>=TODAY(),I2<TODAY()+7),"Coming Soon"

      I'd recommend adding it for "Pending" as well to avoid getting it if I2 is empty:
      AND(I2<TODAY(),I2<>""),"Pending")

  6. Hi, I'm trying to create a cell that will show if the date in one cell is "outdated" or "updated" and a blank return if there is no date listed on that specific cell. I have already create the first part, but I'm having a hard time in introducing the "BLANK" part. I'm new in this kind of stuff and still exploring. Hope you could help.
    Thank you!

    • Hi Eana,

      Use a pair of double quotes ("") to check if a cell is blank (=IF(A2="",...)) or to return a blank cell if the condition is met (=IF(A2=20,"YES",""))

  7. Hello, I am trying to make a sheet that connects to a form. The form will be asking what tools are needed, if they put needed i want it to put a 1 in the column and if its not needed than put a 0. How would i formulate that? i tried =if(B4 "needed", "1", "0") but that did not work.

    TIA!

    • Hello Madison,

      You missed an equal sign after the cell reference. Also, numbers don't need to be in quotes. Please try this formula:
      =IF(B4="needed",1,0)

  8. Hi there, I am trying to create a ticket tracking system that will display pricing based on location and date. I have 5 locations named "1J", "3A", "4Y", "10Y", and "ND" that are listed using Data Validation in column C, and Dates in November are Listed in Column A. I would like to have the price populate in column B based on the Location selected AND if the date listed in column A is before 11/18/19 the price would show one amount, and if the same location was selected but it was after 11/18/19 then it show a different amount. Example: If "1J" was selected on 11/5/19 the price would be "$20", If "1J" was selected on 11/19/19 the price would be "$22" because 11/19/19 is after 11/18/18. I want cell B2 to change if the date is before 11/18/19 and a certain location has been chosen in cell C2. So far this formula works but how would I create a formula that allows for the other 4 locations to possibly be selected and location relevant pricing populate?
    Also FYI 11/18/19=43787
    =IF(AND(A2<43787,C2= "1J"), "$18", "$20")

    • Hello,

      If I understand your task correctly and the prices vary not only based on dates but also on locations, you need to create a nested IF and list each price for each set of conditions, for example:
      =IF(AND(C2="1J",A2<43787),"$18",IF(AND(C2="1J",A2>=43787),"$20",IF(AND(C2="3A",A2<43787),...))))

  9. Hello, I am trying to create an inventory tracker to track multiple items being sold at multiple locations. I have an Inventory sheet that has Product A listed (lets call that A2) and then a numerical quantity under Original Stock at Location A (B2). Then there's Current Stock at Location A (C2). Then I have a Sales sheet. What I'd like is when a sale is recorded for Product A at Location A, for C2 on the Inventory sheet to equal (B2- the new sale). I was able to make an IF formula that works when the sale is recorded in a specific cell, but the sales will be listed in chronological order so I'm trying to make the formula work for the entire column all the way down. Is there any way to do that? Thanks so much!

    • Hello Jamie,

      I'm really sorry but the description is a bit confusing. Please share a small sample spreadsheet (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

      Note. The email address above is 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 do my best do advise you.

  10. I have a column that will a yes or no. Based on the answer, I would like for another column to calculate a tax. Example, if cell F13 is Yes, then I would like to look at an amount in cell G13 and calculate the tax based on the amount in cell G13. Then if cell F13 is No, I would like for it to look in cell G13 and calculate the tax based on the amount in cell G13. Bottom line, if a person is married (Yes) then there will be one calculation and if a person is not married (No) there will be a different calculation.

    • Hello Robert,

      If I'm getting your task clear, a formula like this will do:
      =IF(F13="Yes",calculation_for_YES_with_G13,calculation_for_NO_with_G13)

      In case you're still not sure how it works, please describe how your data is arranged in more detail.

  11. For google sheet
    If(or(A1="",A1="TBD"),"",text(A1,"yyyy")))
    Why this formula returns TBD if A1 contains TBD?

    • Hello Nadeem,

      I'm sorry but the formula you provided can't return TBD in case it's already in A1. A cell with this formula will remain empty in this case.
      I kindly ask you to double-check the formula in your spreadsheet and the contents of A1.

  12. I'm doing of spread sheet of our household bills and want to show the difference from the previous month with some result being + change some being - change. How do i do that?

  13. Hello,
    I'm trying to create a fixed daily summary of the data below.
    Date Fruit Name Quantity
    4-Nov-19 Apple 10
    4-Nov-19 Orange 20
    4-Nov-19 Grapes 50
    4-Nov-19 Banana 15
    5-Nov-19 Apple 20
    5-Nov-19 Orange 50
    6-Nov-19 Orange 20
    6-Nov-19 Grapes 50
    6-Nov-19 Banana 15
    This below is the summary format I want to do
    Summary
    Date Apple Orange Grapes Banana
    4-Nov-19
    5-Nov-19
    6-Nov-19
    ...

  14. I am looking for help to create a formula that will calculate tax liability as follows:
    1.) If annual income is less than $42,900 then tax is calculated by multiplying the tax amount by 5.8%
    2.) If annual income is $42,900 or greater and is less than $101,550 then tax is calculated by multiplying the difference between $42,900 and the annual income by 6.5% then adding $2,488.
    3.) If annual income is greater than $101,500 then tax is calculated by multiplying the difference between $101,550 and the annual income by 7.15% and adding $6,447
    Any assistance would be helpful!

    • Hello Christie,

      Please check if the formula below works as desired. It is written supposing that your tax amount is in column A and annual income is in column B, so just make the necessary adjustments based on your data:
      =IF(B2<42900,A2*5.8%,IF(AND(B2>=42900,B2<101550),(B2-42900)*6.5%+2488,IF(B2>101500,(B2-101550)*7.15%+6447)))

  15. I am trying to add an IF statement that would add a multiplier if they play for a certain team. So like, I have rows of stats, name and team. So IF they play for the rockies they would have a .9375 multiplier. However the thing I need it multiplied to is already a formula. So can I add an if statement to a formula, if so, how?

    • Hello Jackson,

      For me to be able to help you out, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      Note. That email for file sharing only. Please do not email there. Once you share the file, just confirm by replying here.

  16. I would like that if A1 is 0 then put cell B1 in cell C1.
    Is this possible?
    Looking forward to your help!

      • Is it possible to do this but searching for a value in a column?
        I would like that if A1 = value in any cell in a column B, put cell C in the same row in cell D in same row.
        Hope it makes scense.

  17. How do we use the if statement to redirect to a website?
    if value=true
    then the person is redirected to a website.

    • Hello Aditya,

      Using the formula, you can only make it return a webpage for the user. Just enter the hyperlink as an argument, and it will appear if value=true. However, the person will have to click the link in order to open it.

  18. If I want to use this function on existing data, and if the predicate is false I do not want the row to change, how do I go about that?

  19. =IF(B2>=96.01%,"5",if(B2<96%,"4",if(B2<95%,3,IF(B2<94%,2,IF(B2<92%,1)))))
    Help on this please.
    1 96.01%

  20. I'm trying to populate a certain text in column F if column c says a specific thing. I'm using =IF(C3:C237 = "DMM901Y", "No PPW Status Available", " ") on the F column, but it's not working. All help is appreciated. Thank you!

    • Edit:: It works on a singular cell, but not the whole column. I want the rule to work for the whole column.

      • Thank you for your question, Lindsay.

        Try wrapping your IF with an ARRAUFORMULA:
        =ARRAYFORMULA(IF(C3:C237="DMM901Y","No PPW Status Available",""))

        If you're wondering how it works, please check this blog post.

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 :)