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

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

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

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

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

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

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

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

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

  9. 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,

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

  11. Is it possible to copy selective data from one tab of a sheet to another tab. I have data in column A through Y. And, I want to copy only the name (column A and url column (column V) only if the url exist for that name, not if the url column is empty.

    Any help would be appreciated.

  12. Is it possible to create an IF formula to keep a running count of the number 1 in a column of cells, ex. E36 to E64

  13. I am using this IF formula to multiply if the number is greater or = too but it doesn't work past the 1st IF. All are multiplied by 1.15 no matter the number value of the number. Please help!

    =if(isblank(K2),"",if(K2>=45,K2*1.15,if(K2>=40,K2*1.2,if(K2>=35,K2*1.25,if(K2>=30,K2*1.5,if(K2>=25,K2*1.75,if(K2>=20,K2*2,if(K2>=15,K2*3,if(K2>=10,K2*4,if(K2>=1,K2*6,0))))))))))

    1. Your formula works great. I've just checked and if K2=40, the result is 48; if it equals 1, then the result will show 6.

  14. Hi,

    I have two columns which you can choose if the payment made was "CASH" or "CHECK".

    I was successful in setting up the "CASH" part wherein if I choose the first column as "CASH" then the other cell will automatically set the value/text as "N/A".

    My problem is when I now choose "CHECK", I am hoping that the other column will be a free cell, wherein I can write anything or specifically numbers. (for check numbers that were used to pay) without erasing or deleting the formula/function that was set or written.

    Appreciate the suggestions. Thank you.

  15. I am trying to create a payment list that includes a drop down menu on every row (PAID & NOT PAID). Another column is how much each person owes, I7:I105 is the payment status, F7:F105 is how much is owed, F107 is the total money owed altogether. So far ive created the drop down menu on each row, totalled all the money into F107 and when the payment status is set to 'PAID' the whole row will change to red with strike through text.
    What i want is when the payment status is set to 'PAID' for the whole row to turn red with strikethrough text and the money a person has paid to be deducted from the total money owed in F107. HELP!!!!

  16. I am doing a stock sheet, i want to say if a number is entered in the delivery slot, add this number to the remaining stock cell and change the total stock cell accordingly.

    J11 Stock Delivered
    G11 Stock Remaining
    D11 Total Stock

  17. I need a formula that copies data from several cells on one sheet to identical cells on another sheet, IF a value is entered into a cell on one of the sheets.

    if the value in F4 is "x" Then A4, B4, C4, D4, E4 and T4 need to be copied to (new sheet B3,3,D3,F3,G3,H3)

    any help I'd appreciate.

    1. Hello,

      If I understand your task correctly, please enter the following formulas into the corresponding cells on the new sheet:

      Cell B3
      =IF($F$4="x",Sheet2!A4,"")

      Cell C3
      =IF($F$4="x",Sheet2!B4,"")

      Cell D3
      =IF($F$4="x",Sheet2!C4,"")

      Cell F3
      =IF($F$4="x",Sheet2!D4,"")

      Cell G3
      =IF($F$4="x",Sheet2!E4,"")

      Cell H3
      =IF($F$4="x",Sheet2!T4,"")

      Hope it will help you.

  18. Hi, I am trying to populate a cell with a certain value (price) if the previous according to different values in another cell, example: if the day is sunday or monday the price would be 20euro, if is monday would be 15 euro... how shall I type the function? Thank you

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IFERROR(IFS(WEEKDAY(A1,1)=1,"20euro",WEEKDAY(A1,1)=2,"15euro"),"")

      where cell A1 contains a date value, e. g. 1/30/2018

      Hope it will help you.

  19. I am trying to make the IF statement work selecting one of two columns that has data. In every row either Row V or Row W will have data but never both and just want the formula to select which one has data. I have tried the ISBLANK statment but it sees the hidden formulas as data and won't return a value.
    =IF(V2>0,V2,W2) This works is V has a value but if V is blank it won't return W

    =if(ISBLANK(W7),V7,W7) This attempt will work to show the value for W but if V has a value it can't see pas the formula in W.

    Any help would be greatly appreciated.
    I've tried these two without success.
    Tim

  20. I like to have a function like
    if a cell (ex B2) is empty-(blank) then ---- else 100)

    i don t know how to tell him is empty

  21. How do I insert one of several different formulas into a given cell, based on a one-time test?
    For example, if C10:C15 hold 'frozen' (non-recalculating) random numbers, I want B10,say, to hold =E2*3 if C10 is less than 0.5, otherwise B10 should hold =0.
    How do I go about this?

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(C10<0.5,E2*3,0)

      Hope this will help you!

  22. I am trying to write some if and statements .. So I have a number crossword puzzle, if the students get the write combination I want it to say congrats. I am setting for smaller statements - two cells at a time, if I have to .. But I have something wrong.

    =if(AND($A$3="5",$A$5="1"),"so far so good", " ")

    Any help would be appreciated.

    1. Hello, Rachel,

      Please try the following formula:

      =IF(AND($A$3=5,$A$5=1),"so far so good", " ")

      Hope it will help you.

      1. How would I create a custom formula in Google Sheets which would do the following:

        If Column A, B, & C contains a date then change column X to YES

        Cheers legend!!

  23. In Google Sheets, how can I use an "if" conditional to change the color of a row based on one cell in that row? I am using conditional formatting for one cell (G3:G30), but I want the whole row to be the same color of that the color in the G cell in that row. Is there a certain code to indicate color? I am using 4 colors.

    Incomplete=red
    In Progress=orange
    Ongoing=Yellow
    Complete=Green

    I know how to copy formatting for font/size, but not for cell color.

  24. Help me to make a formula. I have given a date e.g: 1st August 2017. The due date is 7th August 2017. If the completion date is before due, it becomes EARLY. If it completed on the date same as due, it becomes ON-TIME. If it passed the due, it becomes LATE. Thanks in advance.

  25. I am trying to pull in the value of C based on the value of W=A and X=B.

    A1= "John"
    B2 = "3/17/17"
    C3= "20"

    W4 = "John"
    X4 = "3/17/17
    Y4 = "20"

  26. Hi there
    Can you please let me know what the proper equation would be for assigning a specific value to a cell? For example, if a cell is populated (with text, doesn't matter what the text is) and I want to assign the value of 1.25 in the cell directly to the right of the populated cell, how would I type that out in an equation? IF C2 = filled, then 1.25 (or something similar).

    Thank you!

    1. If I get it right you'd put the formula in D2, where you will populate 1.25, if C2 has a whatever value. This if it can be whatever value, also a number, ecc... you can use the following formula:

      =IF(C2"",1.25,"Field was empty")

      you can replace "Field was empty with whatever you'd wish to do in case C2 was not populated.

  27. Hi I'm trying to get the if statement to do a subtraction but it's coming up with parse error. What I'd like is a time i.e. 07:00 and the if statement subtracts from that by 0:15 if over 4:30, 0:30 if over 6:00 or 0:45 if over 8:00 how would I best wright this ?

  28. I have 3 columns with three different values, I want a formula which can say Good or bad based on some If conditions.

    Basically what I want is

    A) The difference in values between Col C and Col A > 12
    AND
    B) Value in col C is > 160

    If the above conditions match I want the fourth column to say "Bad" If it fails to match I want it to say "Good"

    This is the formula I have used. =IF(AND(C1-A1 > "12",C1 > "160"),"bad","good") But irrespective I get Good all the time.

    Can you help what is that I am doing wrong here in the formula?

    1. Hi, Chandra,

      Your formula is written incorrectly, it should be like:
      =IF(AND((C1-A1)>12,C1>160),"bad","good")

      You don't need to enclose numbers in double quotes and you need brackets for C1-A1. Please read this article about common mistakes made when writing the formulas.
      Hope this solves your task.

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