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, we will take a closer look at how Google Spreadsheet IF function works and what advantages we can get from using it.

What is IF function in Google Sheets?

Whenever we use IF function, we 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 how a decision tree may look like:

Decision tree of the alternative question.

So, IF function allows us 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 as "=", ">", "<", ">=", "<=", "<>". Now we'll try and ask such a question together.

Usage of the IF function

Let's assume that you are working in the company which sells chocolate in several consumer regions and works with many clients.

This is how your sales data look like in Google Sheets:

Sample sales data for Google Sheets.

We need to separate sales which were made in our local regions from those which were made abroad. To accomplish that, we should add another descriptive field for each sale - a country where the sales took place. Since we have lots of data, we want this description field to be created automatically for each entry.

And this is when 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 we are going to enter a formula. That's why right after we type the letter "i" it will prompt us to choose a function which begins with that same letter. And we choose "IF".

Functions prompts in Google Sheets.

After that, all our 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 - 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 a text "Our Country".

And again, after the comma write in the value of the 3rdargument. 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".

Our function should look like this:

=IF(B2="West","Our Country","Rest of the World")

And F2 returns the text "Our Country".

Google Sheets IF function.

Now, all we have to do is to copy this function down the column F.

Let's examine the other ways of working with 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. Please pay attention that if the text is being used as the argument, then it must be enclosed in double quotes.

IF function and numerical values

We can use numbers for the arguments just as we did it 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 we give our clients discounts based on the total value of the purchase. If the total is more than 200, then the client receives a 10% discount.

For that, we will use column G and name it "Discount". Then enter IF function in G2, and the second argument will be represented by the formula which calculates the discount:

=IF(E2>200,E2*0.1,0)

IF with numbers in Google Sheets.

IF in combination with other functions

As we 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.

Remember the way we figured out the country where we sold chocolate?

Now we can use OR function as a first argument, which will return "YES" if at least one of the listed conditions is true. We simply change the IF function in the cell F2. For the first argument, we will list all the regions which 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.

We can also use the IF function itself as an argument for the bigger IF function.

Let's assume that we have set stricter discount conditions for our clients. If the total purchase is more than 200 units then they get a 10% discount; if the total purchase is between 100 and 199 then 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 we used IF function as a second argument of the IF function. In this case, the decision tree is as follows:

Nested IF decision tree.

But what if we make it even more fun and complicate the task? Imagine that we're offering the discounted price to the one region only - "East".

To do that correctly, we add the logical expression AND to our function. The formula then will 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 have reduced greatly while their amount remains intact.

We 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 to data processing in Google Sheets.

You may also be interested in:

215 Responses to "Google Sheets IF function - usage and formula examples"

  1. Chandra says:

    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?

  2. Jerry says:

    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 ?

  3. Catherine Van Biber says:

    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!

    • Mikael says:

      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.

  4. Kelly says:

    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"

  5. KORI says:

    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.

  6. Marc says:

    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.

  7. Rachel says:

    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.

  8. Rick Darwin says:

    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?

  9. Chris Harrison says:

    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

  10. Tim says:

    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

  11. susanna says:

    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

  12. Mack says:

    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.

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

  13. Stu says:

    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

  14. Dylan says:

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

  15. Jape says:

    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.

  16. Christian says:

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

  17. Andrew says:

    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

  18. Rayne says:

    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.

  19. Taylor Dean says:

    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!

    • Nick Mikhuta says:

      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.

  20. Ray says:

    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,

  21. John Doe says:

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

  22. Malls says:

    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

    • Nick Mikhuta says:

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

  23. Jeff says:

    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

  24. Cindy Clarke says:

    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!

  25. JENNIFER HARTSELL says:

    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.

    • Doug says:

      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.

  26. Diane B-H says:

    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

    • DIane B-H says:

      Nevermind!! I figured it out.

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

      • Nick Mikhuta says:

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

  27. MARY says:

    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

  28. Di says:

    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

  29. bridy forder says:

    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

  30. Zak says:

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

  31. Dwight says:

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

  32. leigh says:

    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

  33. Doug says:

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

    • Leigh says:

      Thank you Doug.

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

      • Doug says:

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

  34. Jono says:

    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.

  35. teri says:

    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?

  36. Sarah K says:

    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?

  37. ANTHONY CHIMA says:

    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.

  38. gian says:

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

  39. Doug says:

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

  40. gian says:

    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

  41. Anne says:

    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!

  42. Doug says:

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

  43. Sean says:

    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)

  44. Doug says:

    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.

  45. Kate Entwistle says:

    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!

    • Thank you for contacting us, Kate.

      First, if you want to check for any airport occurrences, you need to use the correct wildcard characters - *Airport*

      Also, you used one excess opening bracket for your AND functions. It should be like this:
      (AND(A1="Sedan",B2="*Airport*")

      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 is, 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",B2="*Airport*"),"$350",IF(AND(A2="SUV",B2="*Airport*"),"$400",IF(AND(A2="minibus",B2="*Airport*"),"$$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.

  46. Cathy says:

    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!

  47. Tarun says:

    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.

    • 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!

  48. Brenda Comstock says:

    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?

    • Brenda Comstock says:

      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.

  49. Pascal says:

    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 ^^

  50. Jamie says:

    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

  51. Jeffrey says:

    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

    • 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? ;)

  52. Everett says:

    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?

  53. Amy says:

    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?

  54. Ashley says:

    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"

  55. Becky says:

    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.

    • Becky says:

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

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

  56. Ryan Franks says:

    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?

  57. Janel says:

    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?

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

  58. Kowshik Bokka says:

    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.

  59. Nikki says:

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

  60. Nickyc says:

    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?

  61. Paul says:

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

  62. Debbie says:

    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.

      • Debbie says:

        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.

  63. Jessica says:

    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?

  64. Dan says:

    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.

  65. Raul says:

    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

  66. Shoirty says:

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

  67. Amor says:

    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.

  68. Prajwal M.A says:

    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

  69. Jeff says:

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

  70. Laura says:

    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.

      • Laura says:

        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?

  71. Jonathan says:

    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!

  72. MR Ranajit says:

    I have problem as below:
    Payment method Condition Amount Merchant TDR % Condition Amount Merchant TDR %
    Red Greater than 1000.01 1.05 Lesser than 1000.01 0.10
    Yellow Greater than 1000.01 1.05 Lesser than 1000.01 0.10
    Green Greater than 1000.01 1.05 Lesser than 1000.01 0.10
    Blue Greater than 1000.01 1.05 Lesser than 1000.01 0.10

  73. Ranajit says:

    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.

  74. Rubie says:

    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?

    • Rubie says:

      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.

  75. Brian says:

    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.

  76. Doug says:

    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.

      • Doug says:

        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.

        • Doug says:

          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.

  77. Sara Macurio says:

    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.

  78. Danny says:

    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.

  79. Alejandra says:

    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.

  80. Brian H. Svendsen says:

    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

  81. Nikola says:

    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?

      • Nikola says:

        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

  82. Nikola says:

    Hello, some help here?

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

      • Nikola says:

        Thank you!
        Looking for your response.

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

  83. Nikola says:

    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

  84. Nikola says:

    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.

  85. Ashley Moran says:

    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.

  86. William says:

    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.

  87. Mark says:

    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.

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

  88. Jeff says:

    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.

  89. Susan says:

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

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

  90. Annie says:

    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?

  91. lee says:

    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!

  92. EF says:

    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.

  93. Fran DiGiano says:

    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.

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

  94. Robyn says:

    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.

  95. Missy says:

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

  96. daniel speiss says:

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

  97. daniel speiss says:

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

  98. Alison says:

    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?

  99. KK says:

    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?

  100. Laura O says:

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

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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard