Comments on: Google Sheets IF function - usage and formula examples

Get to know Google Sheets IF function better with this tutorial: when it's used, how it works and how it contributes to a much simpler data processing. Formula examples are included! Continue reading →

Comments page 9. Total comments: 610

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

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

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

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

    1. Hello,

      I'm afraid IF is not the function you'd normally use to find matches.

      You can either try the add-on described here to lookup matches by all conditions (date and fruit), or create a pivot table out of your source data.

  4. 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!

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

  5. 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?

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

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

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

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

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

  8. 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?

  9. =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%

  10. 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!

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

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

  11. MILES RATE TOTAL
    34 1.88 $366.22

    How do i put a mileage range of numbers like this
    miles rate
    0 to 5.9 = 1.01
    6 to 10.9 = 1.11
    11 to 15.9 = 1.25
    and so on in to a formula any help would be great.

    1. Hello Patrick,

      If I understand your task correctly, here's how you should put that down to the formula (supposing everything starts from A1):
      =IF(AND(A2>=0,A2<=5.9),1.01,IF(AND(A2>=6,A2<=10.9),1.11,IF(AND(A2>=11,A2<=15.9),1.25,IF(AND(A2...),1.88,IF...))))

      1. Were you able to get this to work? I tried something similar and am returning an error message.

        =IF(AND(D30>749,D301625,D30=2500),$1500)))

        1. Hi Ben,

          You miss the comparison operator in your second condition: D301625
          Also, I don't see the point of using AND to check whether D30 equals 2500 and is more than 749 at the same time. :) It is either equal to 2500 or not.

          If you clarify your conditions, I might be able to help you with the formula.

  12. Thank you so much for this page.
    Because of it I was able to figure out how to make text in one column trigger text in another.
    Formula
    =IF(OR(F2="Planning"),"BLACK",IF(OR(F2="Started"),"RED",IF(OR(F2="Hold"),"BLUE",IF(OR(F2="Complete"),"GREEN"))))

    1. Thank you for your feedback, Laura.

      Your formula looks nice :) But there's no need to use OR. It is used to check if at least one of the specified conditions is met, like this: =IF(OR(F2="Planning",F2="Started"),"BLACK",...

      Thus, you can get by with the following:
      =IF(F2="Planning","BLACK",IF(F2="Started","RED",IF(F2="Hold","BLUE",IF(F2="Complete","GREEN"))))

      You can also add one last part - double quotes - to keep cells empty if none of the conditions are met: ...,IF(F2="Complete","GREEN",""))))

  13. So, I want a value to appear based on 2 cells, not one. So like A1 has to be 2 & A2 has to be 4 in order for the value/text to appear. Is there a formula for that?

  14. I'm looking for a formula to look at a range of cells in a column and look for someone's name. If the name is there, I'd like it to return "Yes"; if it is not there, I'd like it to return "No." I can't quite seem to get it to work! Is there a way to do this?

    1. Alison,

      You need to wrap COUNTIF with IF.
      Suppose your names are in column A. You can try this formula in B1. Just replace NAME in double-quotes with a name you want to scan for:
      =IF(COUNTIF($A$1:$A$20,"NAME")>0,"Yes","No"))

      Then just copy the formula down the B column and fill with other names to look up.

  15. update, something like this
    =IF(F32=0,"N",IF(F32=,"W")))

    1. Daniel,

      to help you out with a formula, I need more details. What should the formula return if cells are less than or equal to -1? And if more than or equal to 1?
      Btw, we mentioned nested IF in the last point of this article, please have a look.

  16. trying to create a formula for win loss and neutral using cell=1>, <-1 , 0, for neutral.

    If it's more than 1 i want it to show as a "W".

  17. Hi, I have a log for voicemails that includes vm number, client name, date in, date out and who it was forwarded to. What I am trying to do it autopopulate on another tab what calls were sent to whom. so every time I put Missy in the who it was forwarded to column I would like that to fill in a different cell on another sheet with the information from that row. Is this possible??

    1. Hi Missy,

      I believe Fran above had a similar question. Please have a look.
      If Vlookup doesn't help you, please consider sharing 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 tables to 10-20 rows.
      I'll look into it and try to help.

  18. How do you make random web-scraped data consistent?
    Data values are: 134M , 54B , (230K)
    I have a couple functions for making the Millions to Billions consistent.

    =if(ISNUMBER(H37), H37, IFERROR( LEFT( H37 , SEARCH( "k" , H37 ) -1) * 1000, IFERROR(LEFT (H37,SEARCH("m", H37 )-1) * 1000000, IFERROR(LEFT (H37,SEARCH("B", H37 )-1) * 1000000000,) )))

    and this also :
    =left(I37,len(I37)-1)*switch(right(I37,1),"M",1000000,"B",1000000000)

    But how do I make a value in parentheses a negative number?
    Format changing only, doesn't seem to work in web-scraped data for parentheses.

    1. Robyn,

      If we've got your task correctly, the below formula will do the trick:
      =IF(LEFT(I37,1)="(",MID(I37,2,LEN(I37)-3)*SWITCH(MID(I37,LEN(I37)-1,1),"K",-1000,"M",-1000000,"B",-1000000000),LEFT(I37,LEN(I37)-1)*SWITCH(RIGHT(I37,1),"K",1000,"M",1000000,"B",1000000000))

      1. Ahh, Thank you Natalia ! :)

  19. Sheet 1 is a master list showing the name of each volunteer organization in Col. A and its assigned trash cleanup location in Col. B. These groups do multiple cleanups each year on random dates. Sheet 2 is where I enter trash cleanup data by date and organization. (The organizations on the master list in Sheet 1 are just a subset of all organizations doing cleanups.) I'd like entry of the name of organization on Sheet 2, say in Col. A, to recognize automatically its corresponding trash cleanup location and enter it in Col. B. I'm unclear on how to set up an IF statement that would match the name of organization with its corresponding trash cleanup location.

    1. Fran,

      It is Google Sheets VLOOKUP that you should try rather than IF. Here's a formula based on the task and columns you described:
      =ARRAYFORMULA(VLOOKUP(A2:A10,Sheet1!A1:B11,2,0))

      A2:A10 is the column with organization names that you enter on Sheet2. You can extend the range based on the number of cells you're going to fill in.
      A1:B11 is the range with organization names on Sheet1.
      2 - the number of the column with locations on Sheet1.

      To learn more about VLOOKUP in Google Sheets, please visit this blog post.

  20. I have linked a form response to a google sheet. I am looking for help as one of the questions is yes or no if someone would like to be added to an email list. I am trying to write an IF formula to have cell $F2 or $F3 is "yes" than cell $C2 or $C3 (the email) moves to another sheet (i.e. Sheet 2, cell $A2 or $A3, etc). Any help would be appreciated.

    1. EF,

      try this formula in A2 of Sheet2 and copy down the column:
      =IF(Sheet1!$F2="yes",Sheet1!$C2,"")

      Or here's an array formula for A2 that helps to avoid copying the formula further:
      =ARRAYFORMULA(IF(Sheet2!$F$2:$F$100="yes",Sheet2!$C$2:$C$100,""))

  21. Hi,

    I'm looking for some help creating an if-then statement in google docs with conditional formatting. I'd like to populate column E with a 1 or 2 depending on the info in column C. For example, if the name "Cook" is listed in column C then I would need column E to have a 1. Additionally, if column C is Cook or Enright or Sanchez then I would need to mark a 1. TIA!

    1. Hi Lee,

      If I understand your task correctly, place the following to E2 assuming your data starts in C2:
      =IF(OR(C2="Cook",C2="Enright",C2="Sanchez"),1,2)

      As for conditional formatting, it lets you color cells based on conditions. You can learn more about it in this blog post.

      1. Thank you so much! I had the first part correct and appreciate your help! You're awesome

  22. my formula is =IF(C2="Joe Bloggs","Maddie Bloggs","Annie Bloggs") but I want it also be the case if C2=Katy Bloggs and if C2=Rachel Bloggs. How would that look please?

    1. Annie,

      try this one:
      =IF(OR(C2="Joe Bloggs",C2="Katy Bloggs",C2="Rachel Bloggs"),"Maddie Bloggs","Annie Bloggs")

  23. Hi there, I am attempting to create a formula to lengthen the text imputed. ie: dpst = DPST PENDING(c), under conditional format rules, with highlighting & bold font.. I have custom fomula selecte w/ =if (L2="dpst"," "DPST PENDING") apply to range K2:M2.
    I also in the same sheet am trying to create if "conf" is payment type(F), $0.00 balance (O) is due, if Cash, crcd, chk is used, Subtotal (m) - amt pd (N) is balance due (O).
    I have came up w/ =ifs(F4="conf",0,F4="cash","crcd","chk",M4-N4).
    But nothing is working. Please help!! Much appreciated!!

    1. Hi Susan,

      I'm sorry, your conditions for the second formula are quite confusing.

      For us to understand your case and check the formulas, please share your sample spreadsheet with us (support@4-bits.com) with your data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

      Since we keep that email for file sharing only, once you share the file, please confirm by replying to my comment here.
      Thank you.

  24. I want to make a cell time and date stamp if another cell has text. For example, I am having employees enter in marketing information and I want Google Sheets to tell me exactly when they entered in that specific data point. Something like "If cell E2 has text, then cell G2 get's a time and date stamp."

    I thought I might be able to do this with conditional formatting but that is not the case. I'm looking for the easiest fix here with the least amount of code.

    1. Jeff,

      The only function that would return you the date time stamp is NOW. However, the function is volatile - it recalculates itself in all cells each time anything is edited in the sheet. Thus, the standard formula is not an option here, I'm afraid. You need to use scripts to solve the task.

      You may try to find the solution here - an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

      Sorry I'm not able to assist you better at the moment.

  25. What is wrong with this IF statement, "=if(=and(B2 - C2 0) "Close Democrat", =if(=and(B2 - C2 5), "Moderate Democrat", =if(b2 - c2 > 7, "Extreme Democrat", =if(=and(B2 - C2 = -5), "Close Republican", =if(=and(B2 - c2 = -10), "Moderate Republican", =if(B2-C2 <= -10, "Extreme Republican"))))))"
    The error message reads, "Formula Parse Error". The purpose of this IF statement is to check certain percentage differences and give different responses based on them.

    1. Mark,

      You have obsolete equal signs before each IF and AND. Also, the arguments for AND are written incorrectly. Your formula should look like this:
      =IF(AND(B2=0,C2=0),"Close Democrat",IF(AND(B2=5,C2=5),"Moderate Democrat",IF... so on

      Please look through examples above to learn more about the IF formula.

  26. I have a Google Sheet that has multiple tabs.
    If "Head of Household" is entered in Column E on Tab 1, then I want it to bring over the information entered in Column C & D to Tab 2 Columns C & D.
    I also do not want any blanks, so I want Tab 2 to only be Head of Household names listed on Tab 1.

    1. William,

      It looks like VLOOKUP is the function for your task. This is the function one should use when searching for value in one column and pulling the corresponding info from other columns.

  27. Please help. I'm trying to write an if/then formula based on a count. Count the number of cells in a given row range (G1:Z1) with the text 'Completed' then multiple the number by 3.

  28. If the result for Thu, Aug 22, 2019 (1262) was bigger than Wed, Aug 21, 2019 (1124) to highlight the result in a custom colour or if the result is worse - highlight the result in another custom colour.

    1. Thank you for another explanation, Nikola.

      It's a pity the way offered before didn't work as you need.
      I'm passing your task to the same tech specialist - hope he'll be able to help!

      1. Nikola,

        Please try these formulas in your conditional formatting:
        for when yesterday is > than the day before
        =AND($A1=TODAY()-1,OFFSET($B1,-1,0)<$B1)

        and for when yesterday is < than the day before
        =AND($A1=TODAY()-1,OFFSET($B1,-1,0)>$B1)

  29. Hello Natalia,
    Thank you for your response!

    Take a look at this example of my sheet:
    Column A Column B
    Mon, Aug 12, 2019 1695
    Tue, Aug 13, 2019 1463
    Wed, Aug 14, 2019 1395
    Thu, Aug 15, 2019 1325
    Fri, Aug 16, 2019 1267
    Sat, Aug 17, 2019 1396
    Sun, Aug 18, 2019 1100
    Mon, Aug 19, 2019 1510
    Tue, Aug 20, 2019 1238
    Wed, Aug 21, 2019 1124
    Thu, Aug 22, 2019 1262

    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

  30. Hello, some help here?

    1. Hello Nikola,

      Your task is not an easy one, and I've forwarded it to our tech team. As soon as I have a solution, I'll reply right away.
      Sorry for the inconvenience.

      1. Thank you!
        Looking for your response.

        1. Nikola,

          Here's what we've got for now:
          1) =AND($A1<$A2,$A1=$A2-1,$B1>$B2)
          This formula makes sure that the date in A2 is greater than in A1 but the corresponding number is A2 is less than in A1. If your data to compare starts in A1, skip it and apply the formula to your table starting from A2.

          2) =AND($A1<$A2,$A1=$A2-1,$B1>$B2)
          This one does the same but also checks if the difference between dates is 1 day only.

          If these are not exactly what you need, please share a sample spreadsheet with us (support@4-bits.com) with a short example table and the result you expect to get. We don’t monitor the Inbox of that email though, so please confirm by replying here once you share the file. Thank you.

  31. 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?

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

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

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

  33. 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'?

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

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

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

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

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

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

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

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

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

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

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

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

  38. 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?

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

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

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

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

  40. 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!

  41. 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!

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

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

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

  42. 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),""))

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

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

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

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

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

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

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

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

  48. 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?

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

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

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

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

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

          1. That’s great Thankyou. Much appreciated ??

  50. 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().

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

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

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

          1. Yes.

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