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. Hello,
    I have a sheet with 2 columns with 1 with dates and 1 with amount regarding the date.
    I need a formula which can highlight which was grater from yesterday or the day before.
    Something like this :If yesterday is < than the day before, show RED
    Any suggestions?

    • Hello Nikola,

      I'm sorry I'm a bit confused. What "amount regarding the date" do you mean? Can you please give me the examples of what A1 and B1 may contain?
      Am I getting it clear that you need to highlight either A1 or B1 based on your condition?

      • Hello,
        Column A - Column B
        date 1
        date 2
        date 5
        date 8
        date 9

        I would like to use conditional formatting to highlight when -> my yesterday result was better/worse than the day before.
        If yesterday is < than the day before, show RED

  2. Hello :-)
    I want to have my overtime hours update automatically.
    i first tried to use for an example:
    =IF(H14>12,H14-12,0)
    I write it in the I14 cell where i have my overtime. hours over 12 hours.
    But I get an error, but I also do that when I try the one from the article above "=IF(E2>200,E2*0.1,0)"

    Best regards Brian

  3. If using the Check mark on GSheet, care we able to create an "IF" formula, if checked the line moves to the top of the list'?

    • Alejandra,

      If I'm getting your task correctly, I believe you'd better use a filter in Sheets. The IF function cannot move the lines, it only returns some values based on the other cell contents. But with a filter, you can quickly sort the lines so that all checked cells are at the top. Please refer to this article for more details.

  4. i am creating a profit loss spreadsheet for matched betting
    The following columns are used in my calculation
    E Is the bet type as a drop down box
    H Is the commission value
    J is the odds
    K is the stake
    L is the liabilty
    M is the result Win or lose drop down box choice
    N is the profit and loss

    So if
    the bet type is Trigger and the result is win then Profit = odds*stake - profit/100*commission
    the bet type is Trigger and the result is lose the profit =stake*-1
    the bet type is free and the result is win then Profit = odds*stake-profit/100*commission-stake
    the bet type is Free and the result is lose then Profit =Liability*-1 - profit/100*commission

    I have no idea how to write these multiple choice formulas using the choices in the dropdown boxes there are 4 outcomes depending on the choices made in the drop down boxes
    can anybody help

    • Hi Danny,

      It doesn't matter whether there are plain values or dropdowns in cells - the formula will work with whatever cells display.

      It looks like you need to build nested IF formula. We mentioned it in the very last point of this topic.
      So your formula should look like this:
      =IF(AND(E2="Trigger",M2="win"),J2*K2-N2/100*H2, IF(AND(E2="Trigger",M2="lose"),K2*-1, IF(..., IF(...))))

      You need to replace dots with your remaining conditions and copy the formula down to other cells of the column with a formula - everything should work.

  5. I am trying to do a tool inventory sheet that takes my weekly production and divides it by my tool life which equals how many tools I need for the week, I then subtract the current inventory I have in house for a number of tools to put on order. My equation works, but some numbers come out negative, these numbers I need rounded to zero, some numbers are positive decimals, these I need rounded up to the next whole number. I am not sure how to tell google sheets that if it is negative to round to zero, if it is a positve decimal then round to the nearest whole number.

    • Hello Sara,

      The first formula that comes to mind is this:
      =IF(A2<0,0,ROUND(A2,2))

      A2 can be replaced with formulas you use to calculate the total numbers.

  6. I have one column that is debits, one that is credits and one that gives the balance. How would I write a formula for the balance column to subtract the debit column from the balance column IF there's an amount entered, and to add the credit column with the balance column IF there's an amount entered? I'm sure this is probably quite simple and straightforward, but I just can't seem to see it. Thank you to anyone and everyone that can help me.

    • Hello Doug,

      I'm sorry, but the conditions are a bit confusing.
      Which columns exactly you want to check for the amount entered before adding and subtracting?
      Also, you can't place a formula to the balance column AND use the values from this column for calculations at the same time. Cells in Google Sheets may contain either text/numbers or formulas.

      • Hi Natalia, thank you for the reply back! I'll do my best to explain since I can't add a screenshot here. So I've got three columns, column F is my Debit field, column G is my Credit field and column H is my balance field. Each column is titled as such too in Row 1. So my first Deposit entry is on Row 2 of the G column, the Crefit column. I have it set up much like a bank ledger would be, but I'm using it to track my toll charges for the road I use to get to and back from work. Their system is horrible and some toll charges take forever to post. Makes their balance seem like I still have more money then I do on the accountthan I actually do. That's just a little background on what and why I'm doing this. I have a separate column for the tolls not charged yet, got that one figured out no pro bb lem. Ok, so after my deposit entry, my first two Rows are tolls that they haven't charged for yet and are blank in the Debit and of course blank in the Credit field. So my formula is put into H3. H again is the balance field. The formula I have there now is, =H2-F3+G3. Since I have no dollar amount input to either the Debit field (F column) or the Credit field (G column) it basically carries the original deposit amount down to the next box instead of leaving it blank. The third row has a dollar amount that was charged, so going down the balance column it goes $50.00, $50.00, $50.00 then subtracts the next debit input on row 5 of $1.07 and balance comes out to the correct $48.93 amount. It goes like this down the entire coulmn. So, what I want to do is instead of the balance repeating itself in the rows where there is not Deit or Credit input I want that corresponding box in the Balance column to remain blank, till it sees a dollar amount input. From what I've seen in searching the web on how make it accomplish this, it seems that Google sheets has this IF feature that would do this for me but I just can't seem to get it to work. Don't know if I'm just doing it wrong or the things I've found searching the net on how to make this work is incorrect. Hopefully I've explained in better detail on what it is I'm trying to and just didn't make it even more confusing for anyone. Thanks again for any help you may be able to give.

        • This is the latest formula I've tried but it comes back as a formula parse error.

          =IF(OR(ISBLANK(G3),ISBLANK(H3),"",I2-G3+H3)

          • Doug,

            I appreciate the detailed explanation, but there are still some points I'm not sure about in order to replicate everything correctly.
            Please share your 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 tables to 10-20 rows.
            Note. We keep that email for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.

            We'll look into your task and try to come up with a formula. Thank you.

  7. I'm trying to create a way to have a cell copy another cell from a different tab depending on what you type in. For instance if you type 5, it will copy a specific cell from a separate sheets page, or even a specific cell from the same page. If you type 6 it will copy a different cell. Thanks

    • Brian,

      If you're trying to do this in the same cell, I'm afraid it's impossible. Any formulas you'll have in cells will be overwritten with whatever you're trying to enter to the same cells.

      However, you can use a helper column, e.g. keep column A for numbers to enter, fill column B with formulas to return the result based on the number in A.
      So, if you type 5 to A1, have B1 return the necessary cell with a formula like this:
      =IF(A1=5,Sheet2!G1,"")

      Feel free to read more about referencing other sheets in this article.

  8. I'm trying to make a function combining the following functions:

    IF(N3=8,N3=16,N3=24,N3=32,N3<=40), "Extreme", " ")

    All work on their own, I just cannot figure out how to combine them.
    How can I do that? Can it be done?

    • It didn't post the formulas right. I want it to be like this:

      If the value is between 0 and 7, it says Subclinical. If it is between 8 and 15, Mild. 16 and 23, Moderate. 24 and 31, Severe. and 32 and 40, Extreme.

      • Thank you for the clarification, Rubie.

        Here's your formula:
        =IF(AND(N3>=0,N3<=7),"Subclinical",IF(AND(N3>=8,N3<=15),"Mild",IF(AND(N3>=16,N3<=23),"Moderate",IF(AND(N3>=24,N3<=31),"Severe",IF(AND(N3>=32,N3<=40),"Extreme","")))))

        To be able to build these nested formulas, please read through the last part of the article: IF in combination with other functions

  9. There is payment Method Red/Blue/Green/Yellow, Greatger Amt 1000 then charge should be 1.05, Amount less than 1000 charge should be .10

    • I'm sorry, Ranajit, but your task is not clear.

      Please add delimiters into your sample so we could understand where new cells start, for example:
      Payment method | Condition | Amount |
      Red | Greater than 1000.01 | 1.05 |

      Also, would you like to simply return the amount of the charge or calculate it somehow right away? Please describe your task in more details.
      We'll see if we can help.

  10. Hi Laura,

    I need help on creating a formula for my google sheets. It's an inventory by the way. I want this formula to highlight a cell on the first sheet after it comparing from the second sheet. The first sheet would be the seat plan, while the second is the inventory. If the inventory shows that a certain seat doesn't have any desktop deployed, the corresponding seat on the first sheet would be highlighted with red, otherwise green. Appreciate your help on this, many thanks!

  11. Hello,
    I am trying to use this formula to tell me YES or NO. So if Column C has a date, then I am trying to have Column D tell me YES or NO if that date is 60 days away from that date this year 6/17/2019?
    (i.e. ColumnC= 8/17/2018 then I would like ColumnD to populate YES because the date is within 60 days of that same day this year.) Am I using the correct method with IF, and if you can you assist with setting it up correctly because everything I am trying is giving me an error?
    Thank you!

    • Hello Laura,

      If I understand your task correctly and column C contains only dates before today's day, this formula should help:
      =IF((DATEDIF(C2,TODAY(),"YD"))<=60,"YES","NO")

      However, if column C may contain future dates, you need another more complicated formula:
      =DATEDIF(IF(DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))>TODAY(),TODAY(),DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))),IF(DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))>TODAY(),DATE(YEAR(TODAY()),MONTH(C3),DAY(C3)),TODAY()),"YD")

      If it's too difficult for you, you could use our Date & Time Wizard, but at the moment it's available for Excel only.

      • Good morning Natalia,
        Thank you so much for your help! It definitely got me in the right direction. I think the formula isn't working for what I need it to do however. When I added the formula to my sheet it populated YES for every date, even ones that are less than one year ago. So this is never going to have a date in the future it's only going to be past dates but I am basically trying to make a sheet tell me if that date is less than one year old from today's date or more than one year old. Will an IF THEN work for this problem?

        • Good morning Laura,

          Based on your first example, I thought the year is not important and you want to compare days and months only. If you'd like to consider the year as well, here's another formula:
          =IF((DATEDIF(C1,TODAY(),"M"))<12,"YES","NO")

  12. I've been looking for a way to do If statements with Filter(). I know I've done it before, but now I just get error messages. =IF('Totals'!F1='Mesa', filter(Mesa!A:D, Mesa!A:A >= Totals!B1, Mesa!A:A = Totals!B1, All!A:A <= Totals!D1),""))

    • Jeff,

      At first glance, there's one extra closing bracket at the end of your formula. Single quotes at the beginning are also excess:
      =IF(Totals!F1="Mesa",Filter(Mesa!A:A,Mesa!A:A>=Totals!B1,Mesa!A:A=Totals!B1,All!A:A<=Totals!D1),"")

  13. If A2 cell data is entered then B2 should be entered if no it should display error message "please fill b2 cell" what is the formula for this

  14. Hi, I am trying to sum up all the data marked WEB, MOBILE, or APP in every account per day. I have used sumif before to sum up all the data perday in a single column. I cant really explain it through words. Can I just share my draft to you? Thanks.

    • Hi Amor,

      yes, you can create a sample file with your data and formulas you tried to use. Please include the example of the result you'd like to get. Once ready, you can share the file with support@4-bits.com.

      Note. We keep this account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm in this message thread.

      I'll look into your task.

  15. Your example of this function was the best out there. Thank you!! Shorty

  16. In one sheet, I have types of expenditures and their value.

    I wish to have a summary on another sheet that will have a table showing the total spend on each type of expenditure. For example,

    Taxi xxx
    Groceries xxx

  17. Hi, I am trying to create a custom formula that will turn the cell red if the following cells on that row equal a different amount, this is an employee/client spreadsheet, so for example in column A, row 4 and downwards are the client's names, in column B row 4 and downwards is the expected duration of the client's job and in column's C-H row 3 are the employee names and I just fill the decimal time in the sheet below the names corresponding to the client. I have added a conditional format on each cell in column B using Not equal to function and in the value box I have entered =SUMC4:H4, but when I try and copy this into the cells below it gives me the same value for Row 4, I have done this manually but the problem is the spread is divided vertically by Monday to Friday and if I add a new row for a new client or new column for a new employee then the whole sheet shifts and the formulas no longer calculate the correct line? I am new to using sheets and apologise if I am being stupid but please help.

    • Hi Dan,

      For us to be able to help, please share your 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. We keep support@4-bits.com for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just reply to this message.

      I'll look into the task and see if there's a simple solution for you.

  18. Hi, instead of just creating if for a cell, I want to create for an entire row. how do i input a range instead of a single cell? basically i want to create a formula in A1 cell such that if any of the row of celss from B1 to Z1 have "Apple", "Banana" or "Cucumber" written in the cell, I want A1 cell to show "No", if the cell contains anything else, i want A1 cell to show "Yes". Can please help?

    • Hi Jessica,

      Try this one in A1:
      =IF(OR(COUNTIF(B1:Z1,"Apple")>0,COUNTIF(B1:Z1,"Banana")>0,COUNTIF(B1:Z1,"Cucumber")>0),"No","Yes")

  19. I
    I am trying to input a formula for the following on google sheets without success & would really like some help please. I have 3 columns A, B & C containing figures. I need a formula to input into column C so that it calculates the following:-
    If column A is greater than column B then amount in Column C is 0. If Column B is greater than column A then the amount needs to show in column C.
    Thank you

    • Hi Debbie,

      If column C already contains numbers, your formula will overwrite everything.
      I suppose your data starts in the second row. Place the following to C2:
      =IF(A2>B2,0,B2)

      And copy the formula down other cells.

      It returns 0 whenever A is greater than B. In other cases when B is more than or equal to A, the amount from B will be returned to C.

      Please let us know if you have any other questions.

      • Hi
        Many thanks for getting back to me. The Formula you have stated works for the first part of my query whereupon if A is greater than B then column C states 0. However I need to add to this to show if Column B is greater than column A then it deducts the amount & shows the balance in Column C eg. B-A=C (10-5=5) if that makes sense but I don’t know how to add that into the formula. Could you please help?
        Eg. Column A states 10.00 Column B states 11.00 then column C should state 1.00
        Eg. Column A states 5.00, Column B states 4.00 then Column C should state 0.00
        Many thanks
        Debbie

        • Hi Debbie,
          Thank you for the clarification.

          I've adjusted the formula for you:
          =IF(A2>B2,0,B2-A2)

          If A2 is greater than B2, it will return 0. Otherwise (if A2 is less than B2 OR if they are equal), it will substitute A2 from B2 and return the result.

          Or use the formula below to keep cells empty if A and B are equal:
          =IF(A2>B2,0,IF(A2<B2,B2-A2,""))

          Please read the last two paragraphs of the article above to understand how to build these formulas.

          • That’s great Thankyou. Much appreciated ??

  20. Hi,
    Cant use this to know when was the data is updated. =if(a1="",0,NOW())
    Everytime i update other cell. All cell that was edited before also updates to NOW().

    • Hi Paul,

      The NOW() function is a volatile one, meaning it recalculates itself in all cells once anything at all is edited in your spreadsheet.

      • Can you help me to make a formula for this? Thank you.

        • Am I getting it clear that you'd like to put a time(date?)stamp based on when the cell is filled?
          For example, if A1 has nothing in it, B1 doesn't show anything.
          Once the data in A1 is entered, B1 shows the time (or date?) of when A1 was filled. And you need to keep this time/date the next time you open and edit the file.
          Am I right?

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