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 11. Total comments: 607

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

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

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

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

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

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

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

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

  9. Im trying to do an if statment that will allow the cell to change fill color on sheets. I have figured out the if, now i just need something like a than. Any thoughts on what i should do?

    1. Hello Nickyc,

      The IF function is usually used to return values into cells. If your task is to color cells differently based on various conditions, you need to create a couple of conditional formatting rules. Please refer to this article to check how to do that correctly.

  10. How do I get the value of F2, to show up in H2, when G2 is blank? It is showing up FALSE currently.
    =IF(OR(G2=5,G2=10,G2=15),"NT", IF(G2="BLANK",F2))

    1. Nikki,

      here's a formula for you to try in H2:
      =IF(OR(G2=5,G2=10,G2=15),"NT",IF(ISBLANK(G2)=TRUE,F2,""))

  11. Data from a sheet which has data of two products. The graphs for both products need to be different. How is this possible? the graph plotted for the value of a certain value in the graph. the sheet contacts a column for product and for the value specified in the previous sentence.

  12. Hi I need a formula for if column A is populated (by a date), then column D will show a date 3 days later and column E will show a date 21 days later. Can this be done?

    1. Hi Janel,

      If I understand your task correctly and cells in column A contain either date or nothing at all, the following formula will do for column D (assuming the data starts from the second row):
      =IF(ISBLANK($A2),"",$A2+3)

      And here's the one for column E:
      =IF(ISBLANK($A2),"",$A2+21)

      You need to copy the formula down to other rows. The formulas won't return anything if cells in A are blank.

      Or you can use the array formula like this in D2 (adjust accordingly for E2):
      =ARRAYFORMULA(IF(ISBLANK($A:$A),"",$A:$A+3))

      It will automatically calculate the result for each row of your table.

  13. Couldn't find an example like the situation I'm in, so what I'm looking to do is see how many instances of a cell exist in a range based on if an associate of that cell equals a specific phrase, in essence I'm looking to do something like this; if(A2,"Phrase") then countif($A$3:$A$1500,A3). I keep running into a different size error, any thoughts on how what I'm looking to do could be done?

    1. I tried to build a sample formula based on what you described, and this one worked for me:
      =IF(A2="Phrase",COUNTIF($A$3:$A$1000,A3),"")

      Please specify if I've got something wrong.

  14. I have a spreadsheet of data based on testing of kids. I have the cells programmed to meet our criteria =IF(AND(F5>80%, G5>=1, H5>=1), "X" , " ") My problem is that the next three columns =IF(AND(F5>80%, G5>=1, H5>=1), "X" , " ") build on that same data and I can't figure out how to only have one column with an "X" Ideally, I would only like the highest column to have the X, not all. Thank you for any help you can share.

    1. Correction: The first column has: =IF(AND(F5>80%, G5>=1, H5>=1), "X" , " ")
      The second column has: =IF(AND(F5=100%, G5>=2, H5>=2), "X" , " ")
      As you can see, if I child had 100% in F5, they meet the criteria for both columns, but we really only want the highest one to have the "X".

      1. I believe adjusting the condition a bit will help you. You need to exclude that 100% from your first formula. Just add one more AND that will check that F5 is both greater than 80 but less than 100:
        =IF(AND(AND(F5>80%,F5<100%),G5>=1,H5>=1),"X","")

        Hope this helps.

  15. How do I apply this formula to every cell in a column?
    I.E. If any cell in column A says "this" then the cell adjacent, in Column B would say "that"

    1. Either enter the formula to the first cell and then copy it down all other rows, or use the array formula like this:
      =ARRAYFORMULA(IF($A:$A="this","that",""))

  16. I'm quite new to Google Sheets, but understand If/Then from another software. May I ask if there is a way to use IF to populate a new sheet? Meaning, if the cell has x, then the contents of that row should copy to another spreadsheet?

    1. There's a special IMPORTRANGE function that helps to return the entire range of cells from another sheet. Here's an example formula for you:
      =IF(Sheet2!A2="X",IMPORTRANGE("spreadsheet_url","Sheet1!A2:Z2"),"")

      Hope this will help.

  17. If I have a numerical value of 38 in A1. And I want a factor of 8.2 to show up in B1 it would be written as follows;
    =IF(A10=38,8.2)
    But what if I had multiple rows with different numerical values and I wanted a factor based on the numerical value as follows;
    A B C D
    1 40 8.8
    2 42 9.0
    3 44 9.5
    4 46 10.0
    If I enter 44 in column A it would enter a factor of 9.5 in column B. If I enter 42 in column A it would enter a factor of 9.0 in column B. How would I formulate that?

  18. Hi,

    Great article! Many thanks. Although good examples, unfortunately I wasn't able to successfully apply them to my sheet.
    I'm trying to apply the following logic:
    If H24 = smaller than 68600, then H24*36,93%, If H24 = > 68600 then take maximum 68600 * 36,93%
    And in the next cell:
    If H24 = > 68600, then (H24-68600)*50,5%
    Can you help with the formulas for this?
    Many thanks in advance

    1. Hi Jeffrey,

      I believe you need to simply switch "=" and ">". Also, make sure you separate percentage decimals by a full stop rather than a comma. The correct formula would be:
      =IF(H24<68600,H24*36.93%,IF(H24>=68600,68600*36.93%,""))

      I guess the same mistakes were made in the second formula. Will you be able to adjust it based on my example? ;)

  19. Hi,
    Is it possible to give an IF to more than 1 rule at the same time?
    I'm trying to create a formula to calculate someone's commission in Google.Doc's

    The rule is if they reach their 100% target, the Sales £ they have earned will be multiplied by 1%. IF they exceed their target (E.G they achieved 125% of their target). the 100% achieved will still be multiplied by 1% but the extra 25% they earned will be multiplied by 1.25%. How do you make it so ONLY 100%> is at the higher rate of 1.25%

    I've gotten this far so far: =IF(B7>1,(B6*1%)

    Thank you

    1. Hi, Jamie. You need a nested if. Look:
      =IF(AND(B7>1, B71.25, B6*1.25%))

  20. been trying to make a formula in google sheets
    what I want to make is there should be a dropdown menu for Yes or No, but only if another cell in another sheet says No, because if that one says Yes there shouldn't be a dropdown menu and it should just say Yes. And the opposite is also true. Of course it would be cool if the dropdown menu still works and it's just linked to that other cell.

    What does work is
    =IF('Side Quests'!A50="Yes";"Yes";"No")

    but then I can't use the dropdown menu anymore and that would mean I can only change the value on that one sheet and not on the one where this formula is. Is there something that I'm missing here? Is it possible to link both cells to eachother, in a way that they both have the same dropdown menu, and if you change one, the other will change to the same?

    Thanks in advance ^^

  21. I have a google form that submits the email address of the user and enters it to field A2. I have another field, B2 that is calculated to capture that email address. The problem: The form goes through 3 levels for approval, and at each level the email is captured and replaces the information in field A2. My goal is to capture the applicant email address, which is the first email entered.
    I am also using an arrayfunction that completes all rows. I can't figure out how to make it only get the first entry. Can you help?

    1. Clarification: My goal is to capture the applicant email address, which is the first email entered in A2 by copying it to B2 and not have B2 change again when A2 captures the next email address in A2.

      1. Hello, Brenda,

        I'm sorry, but there's no way to prevent formulas in Google Sheets from recalculating. There are a couple of spreadsheet settings that can reduce the number of recalculations, but only for few formulas. You can check the list by going to File > Spreadsheet settings > Calculations.

        I wish I could help you better.

        1. thanks

  22. I am trying to make a formula for my math teacher to do grades on Google Sheets. I am trying to accomplish the following: If column B has the text "A-" or "A" or A+", then column C should be filled with the number 4, if column B has the text "B-" or "B" or B+", then column C should be filled with the number 3, if column B has the text "C-" or "C" or C+", then column C should be filled with the number 2, if column B has the text "D-" or "D" or D+", then column C should be filled with the number 1, and if column B has the text "F-" or "F" or F+", then column C should be filled with the number 0.

    1. Thank you for the detailed description of the task, Tarun.

      If you'd like to do that with the IF function, put the following to C2 and copy the formula down (assuming the data in column B starts in B2):
      =IF(LEFT(B2,1)="A",4,IF(LEFT(B2,1)="B",3,IF(LEFT(B2,1)="C",2,IF(LEFT(B2,1)="D",1,IF(LEFT(B2,1)="F",0,"")))))

      Alternatively, you could create a "helper table", where column E would contain the list of all grades, and column F would have corresponding grades. Then, the following should also do:
      =VLOOKUP(B1,$E$1:$F$15,2,FALSE)

      Hope these formulas help!

  23. Can you help me write a correct formula?
    Calculate percentage of difference between two cells when both cells are non-zero.
    Currently have the following but getting error - not sure how to check for non-zero cells and only proceed with calculation when they are.

    =IF ((G419>0)AND(G445>0)),(((G445-G419)/G445)*100)

    Thank you!

    1. Cathy,

      Please try the following:
      =IF(AND(G419>0,G445>0),((G445-G419)/G445)*100,"")

      If you'd like to know how to build the formula like this correctly, please look through this article. It's written for Excel, but the function works the same in Google Sheets.
      Hope this helps!

  24. I have a column in my sheet containing fares that I want to auto-populate based on a vehicle type AND a route:

    Column A : vehicle
    Column B: route
    Column C: fare

    Routes originating at an airport (but not a specified airport) cost more than others so the formula I am trying to use (in column C) but gives #ERROR! is:

    =if(AND((A1="Sedan",B2="%Airport%"),"$350","$330", if(AND((A1="SUV",B2="%Airport%"),"$400","$350", if(AND((A1="minibus",B2="%Airport%"),"$$550","$500"))))))

    If anyone can help me out with where the parse error is that would be awesome.

    Thanks!

    1. Thank you for contacting us, Kate.

      First, if you want to check for any airport occurrences, you need to use the ISNUMBER+SEARCH combo instead of wildcard characters.

      Also, you used one excess opening bracket for your AND functions. It should be like this:
      AND(A2="Sedan",ISNUMBER(SEARCH("airport",B2)))

      Once you fix that, the formula will return another error because you used more arguments than the function allows. You see, if your first condition is met, the formula will return $350, if not - $330. That's it, this is the formula.

      In order to check other conditions, you need to replace all the second numbers with your next IFs:
      =IF(AND(A2="Sedan",ISNUMBER(SEARCH("airport",B2))),"$350",IF(AND(A2="SUV",ISNUMBER(SEARCH("airport",B2))),"$400",IF(AND(A2="minibus",ISNUMBER(SEARCH("airport",B2))),"$$550","$500")))

      If this is not what you need, please send me the example of your table (10-20 rows) to support@ablebits.com with the link to this comment.
      I'll do my best to advise you.

  25. Hello, Sean:
    Try this:
    =IF(ISBLANK(Inventory wk5!I2),Inventory wk5!I2,Inventory wk4!I2)
    The ISBLANK function tests to see if the cell is empty, which is what I think you're testing. If you want to see if the cell is 0 then the formula should say =0. The zero character is a character and the cell would not be empty. Same thing for non-printing characters in cells. Those cells are not empty.

  26. I am trying to create a formula to reference a cell from one sheet (if filled) or another sheet (if the first is not filled). This is what I have and it is not working.

    =IF('Inventory wk 5'!I2>=0,'Inventory wk 5'!I2,'Inventory wk 4'!I2)

  27. Anne:
    Where column 1 is column A and the data is in A2 enter this in column 2:
    =IF(A2<=40,A2,40)

  28. I have a super simple scenario but don't know how to do this. I have 2 columns and I want column 2 to be: 'If the value in column 1 is less than 40, the column 1 value should appear as is. If the value is above 40, then the number 40 should appear. Any help would be appreciated! Thank you!

  29. yeah, sorry it's work now..but i just wondering why it is didn't work before...well..can you help me to add an additional features like days of due date pass? the formula of spreadsheet its really hard for me than programming lol

  30. Gian:
    The formula you have here should work. What is wrong with it?

    1. Logical error

    2. No that is error.

  31. =if(and(isblank(a1),b1<=c1),"Due Date","")
    error. i want that if a1 is blank and b1<=c1 then "Due date" else "";

  32. I need help, I am trying to get my excel sheet to populate an answer from a drop down selection. I need to make a simple "yes, no" drop down selection where when i pick one of the outcomes the cell next to it will come up with the different options to select from. For instance, if i select yes, the column next to it will automatically populate with some drop down list of options to choose from where if i select no then another separate list of option will pops up. Please help if you can. Thank you.

  33. I'm trying to create a formula in my sheet which would turn one cell grey if another cell contains a certain word. For example If C2 contains the word "clinic" I need E2 to turn grey. However, C2 contains the name of the class along with the word "clinic" in some cases, which has me stuck. Can I do this?

  34. I am trying to write a formula that will read a value and if the value is for example an A, B, or C then fill the next cell with a P1. But if it is D, E, or F, then P2. Can this be done?

    1. Hello, Teri,

      Sure. Assuming the value you check is in B2, you need to put the following into your "next cell":
      =IF(OR(B2="A",B2="B",B2="C"),"P1",IF(OR(B2="D",B2="E",B2="F"),"P2",""))

      Hope this helps.

  35. Ohk I am trying to create a formula for the following. I am using a booking form that filters the number of beds and baths into an excel spreadsheet. But what I need for it to do is calculate the price depending on the number of x beds and x baths:

    The parameters are:

    A customer books a 'Regular Cleaning Service'
    A 1 bed, 1 bath = $89.00
    A 2 bed, 1 bath = $109.00
    A 3 bed, 1 bath = $129.00
    A 4 bed, 1 bath = $161.00
    A 5 bed, 1 bath = $177.00

    However if a customer was to add an additional bathroom it would cost another $32.00.

    A customer books a 'Spring Cleaning'
    A 1 bed, 1 bath = $127.00
    A 2 bed, 1 bath = $147.00
    A 3 bed, 1 bath = $177.00
    A 4 bed, 1 bath = $237.00
    A 5 bed, 1 bath = $267.00

    However if a customer was to add an additional bathroom it would cost another $30.00.

    A customer books a 'End of Lease Cleaning'
    A 1 bed, 1 bath = $292.50
    A 2 bed, 1 bath = $360.00
    A 3 bed, 1 bath = $450.00
    A 4 bed, 1 bath = $600.00
    A 5 bed, 1 bath = $900.00

    However if a customer was to add an additional bathroom it would cost another $90.00.

    1. I am just a complete novice at this thing and would love some help :)

  36. Leigh:
    If these are the only three possibilities then
    =IF(B11="Option 1",R21,IF(B11="Option 2",R28,R36)

    1. Thank you Doug.

      How would I add a 4th / 5th ... option?

      1. Leigh:
        You can add a few more IF to the formula like this:
        =IF(B11="Option 1",R21,IF(B11="Option 2",R28,IF(B11="Option 3",R36,IF(B11="Option 4",R37,IF(B11="Option 5",R38,Value if not Option 1,2,3,4 or 5)))))

  37. I am trying to copy a value from different cells on the same sheet, dependant on the option specified in cell b11.

    for example:

    if b11 = option 1 input value from cell r21
    if b11 = option 2 input value from cell r28
    if b11 = option 3 input value from cell r36

  38. Well Done!. I have seen a lot of examples. Yours was spot on!

  39. Want to scan 3 cells for text equal to "Scheduled". If yes, then add a number of minutes, (each cell takes a different number of minutes) if no add 0. Add up the total number of minutes required to schedule. I don't want to save values to another hidden cell and the add up if possible.

    These are what I tried.
    =(=IF(Sales!B5="Scheduled",20,0)+(=IF(Sales!D5="Scheduled",60,0)+(=IF(Sales!F5="Scheduled",90,0))))

    Or

    =SUMIF((=IF Sales!B5="Scheduled",20,0) (=IF Sales!D5="Scheduled",60,0)+(=IF Sales!F5="Scheduled",90,0))

    1. =SUMIFs(Sales!B5="Scheduled",20,0, Sales!D5="Scheduled",60,0, Sales!F5="Scheduled",60,0)

      Didnt work either

  40. Hello,

    I am looking for a formula. I am working on two sheets, sheet 1 I am transferring info that needs to be tracked and I have set all the formulas for pulling over the data I need. However, I don't want the data transferred to sheet 2 unless one certain cell has data. Only needing a portion of what is on sheet 1. As is now, sheet 2 is pulling over all info. So there is a column in sheet 1 that if blank I don't want the data to transfer.
    Thank you

  41. Hi,

    I need help with a formula...

    I would like B62 to change red if J62<0. I could not find any examples or other's questions that resembled this...

    I have tried several formulas like:
    =if(J62<0) with and without quotes, commas, etc.

    Please help?

    Thanks in advance,

    Di

    1. PS...

      I also changed the formatting style to red and bold...

      Thx!

      Di

  42. Goodmorning. Please I need help to get a formula to find the grades of a total mark. Here are the grades below :

    80%-100%:1, 75%-79%:2, 70%-74%:3 and so on

  43. Wondering how to do the following:
    C3 = RED, YELLOW, GREEN
    If RED, then C7 = RP
    If YELLOW, then C7 = YP
    If GREEN, then C7 = GP

    Can I put more than one OR together??

    TIA!
    Diane

    1. Nevermind!! I figured it out.

      =if(C3="RED","RP",IF(C3="YELLOW","YP",IF(C3="GREEN","GP")))

      1. I'd rather use the IFS function. It's designed just for such cases. Its syntax is easier. Look, instead you can put:
        =IFS(C3="RED", "RP", C3="YELLOW","YP", C3="GREEN","GP")

  44. Need a formula to search column C for a SKU number and place the price of the SKU in column H.

    Something like if column C = 30066 then enter $15.00 in column H.

    I would have multiple SKU's in column C and would need to do the formula for each SKU.

    1. Jennifer:
      With the data you provided the formula to give you the result you want is: =IF(C37=30066,15,"T")
      Where the SKU is in C37 you can enter this in an empty cell and format the result cell as currency. The formula says if C37=30066 then display 15 otherwise display "T".
      Depending on the number of SKUs this approach will probably need to be modified to a nested IF, VLOOKUP or INDEX/MATCH. If the list of SKUs gets over ten or so another approach might be required.

  45. I am creating a google sheet to use to balance my checkbook. I currently have it as a basic ledger but I want to create a formula to balance it with my current balance in my account. I need a formula that does this: I want to take the value of column G and subtract the values in all column D fields that have the "N" in column F
    (Column G is my current balance, "N" in column F means it has not yet cleared my account, column D is a transaction amount that has not cleared yet). I tried this formula but it only deals with one row, not every row that has a N in column F. =IF(F453="N",MINUS(D453,G453))

    Thank you!

  46. In cell B2, I am looking for the following Values:
    A,A1,A2,B,B1,B2. If any of the values are TRUE I wish to populate K2 with the letter M and if none of the values are found (False), I wish to populate K2 with F (The populated values stand for Male and Female.)

    I'm having problems with my IF formula. Please help.

    Thanks

    1. Here you can you OR as well, like I described above. In you case K2 formula will be: =IF(OR(B2="A", B2="A1", B2="A2", B2="B", B2="B2"), "M","F")

  47. I am trying to do a nested IF statement for the following scenario:

    In Row 1, Col B:E, I have letters A and C alternating in some of the cells but not all
    In Col 1, Row 2:10, I have locations either Austin or Chicago (a location in every cell)

    Inside the matrix (B2:E10) I want to put in a formula to mark an X in each cell IF it meets either of these conditions, otherwise leave it blank:
    IF($B$2="A" AND A1="Austin", "X", IF($B$2="C" AND A1="Chicago, "X", "")

    This formula is not working though, how do I make a new one that will?

    Thanks

    1. You can't use AND or OR like you did. They have to have arguments, for example: AND(A2 = "foo", A3 = "bar")

      Thus, according to their syntax, your formula will be the following:
      =IF(AND($B$2="A", A1="Austin"), "X", IF(AND($B$2="C", A1="Chicago"), "Y",""))

  48. how would you activate it if there is any value in the cell given in the formula

  49. Hi There,

    Trying to use an IF formula to show that should a cell have a greater amount than another cell a different cell would show 'yes' and would show 'no' if it was not a greater amount.

    For context this is for a stock check.

    Thanks,

  50. Hello! I need help creating a formula for a spreadsheet. If a cell contains a certain range of numbers, how do I make the cell to the right of it, auto-fill in with a designated percentage.

    For example:

    If cell M4, contains any number between 0.00-79.99%, then cell N4 auto fills in with 3%
    If cell M4, contains any number between 90.00-99.99%, then cell N4 auto fills in with 5%

    And so on, based on the following chart.

    % to Quota Bonus Rate
    0.00%-79.99% 3.00%
    90.00%-99.99% 5.00%
    100.00%-109.99% 7.00%
    110.00%(+) 9.00%

    Appreciate the help!

    1. Taylor, you need to add an extra IF as a logical expression to your formula, please have a look:
      =IF(M4>0,IF(M4<79.99%, 3%,IF(M4<99.99%, 5%, IF(M4<109.99%,7%,9%))),"")

      In this case Google Sheets checks if M4 is more than 0; then it checks whether M4 is less than 79.99 and puts 3% if it is, or keeps checking further. Please take a look at the part "IF in combination with other functions" in this article.

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