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 8. Total comments: 610

  1. Is there a way to make this to not spit out false if the cell is blank? We would like to keep adding to this spreadsheet without a running list of FALSE down column K, if that makes sense.
    =if(F3="Chace: xxx","@ChaceChilds",if(F3="James: xxx","@JamestheRich"))

    1. Hello Cindi,

      Sure. It is double quotes that are used to indicate a blank cell. If I'm getting your task right, try adding the pair as the third parameter at the very end of your IF:
      =IF(F3="Chace: xxx","@ChaceChilds",IF(F3="James: xxx","@JamestheRich",""))

  2. I am creating a Workbook for my organization and am trying to pull data from different sheets into another sheet. I understand how to do that but I want one cell to pull data on the page from a different sheet, but then a different cell on that same page to read the data from the first cell and then pull the data from another sheet. I hope that makes sense.

    1. Hello Jason,

      I described different ways of pulling data between sheets in this blog post. You can also incorporate them to your IF function to check some condition first and then pull the needed data based on it.

  3. I am trying to create a If formula the if x=1 then pull results from another tab and cell. x could equal 1-5. When I creat this function: =if(F$12=1,'Tier Monthly Incomes'!B2,if(F$12=2,'Tier Monthly Incomes'!C2),if(F$12=3,'Tier Monthly Incomes'!D2),if(F$12=4,'Tier Monthly Incomes'!E2),if(F$12=5,'Tier Monthly Incomes'!F2)) .

    I am getting an error message that says, "Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 6 arguments." Can you think of a way that I could make this work or suggest an alternative way to address this? Thanks!

    1. Hi Tom,

      The problem is that you put a comma and each new IF after the closing bracket, while they should go inside of those, like the very first one you built. In other words, you need to move three first closing brackets to the very end of your formula:
      =IF(F$12=1,'Tier Monthly Incomes'!B2,IF(F$12=2,'Tier Monthly Incomes'!C2,IF(F$12=3,'Tier Monthly Incomes'!D2,IF(F$12=4,'Tier Monthly Incomes'!E2,IF(F$12=5,'Tier Monthly Incomes'!F2)))))

  4. Hello
    I am trying to create a formula but not able to crack it. There are 3 parameters based on which the result is calculated and also there is All parameter which is not there in the data. When I select All it should calculate for all the data and when i select a specific parameter is should calculate for that. I also need the result based on any combination. This is the formula i have come up with but it does not populate result If I select All for all the parameters.It does only for one selection of All
    =IF(BD41="All",(SUMPRODUCT(($AY$2:$AY$6600=$BD$2)*($AO$2:$AO$6600=BE41)*($B$2:$B$6600=BF41)*($AQ$2:$AQ$6600=BF42)*1)),IF(BE41="All",(SUMPRODUCT(($AY$2:$AY$6600=$BD$2)*($C$2:$C$6600=BD41)*($B$2:$B$6600=BF41)*($AQ$2:$AQ$6600=BF42)*1)),IF(BF41="All",(SUMPRODUCT(($AY$2:$AY$6600=$BD$2)*($C$2:$C$6600=BD41)*($AO$2:$AO$6600=BE41)*($AQ$2:$AQ$6600=BF42)*1)),(SUMPRODUCT(($AY$2:$AY$6600=$BD$2)*($AO$2:$AO$6600=$BE$41)*($C$2:$C$6600=BD41)*($B$2:$B$6600=BF41)*($AQ$2:$AQ$6600=BF42)* 1)))))

    Please help

    1. Hello Sandeep,

      I'm really sorry but I'm afraid we won't be able to trap the error without seeing your data.

  5. Hello!
    I am working with a donation spreadsheet that lists the companies and the years 2017, 2018, 2019 in separate columns. In each column for the years, the amount each company donated is in those columns. Some companies donated one year and not the others while others donated all three years. Is there any way for me to sort or filter which companies donated in 2019, in all three years, and the companies who donated in 2017 & 2018 but not in 2019? Would a filter be the best option, or are there ways to do a table or chart of some sort to organize this data by who donated which years?

    1. Hello Katrina,

      The easiest way, to my opinion, is to try our special tool that works like an advanced filter: it will create different lists based on different conditions (empty/not empty) without interfering with your source data.

      There are a few other standard ways to try though:

      1. Filter columns by conditions to meet your criteria. However, you'll need to change filtering conditions a few times to see the donations over different years.
      2. There are also options of creating a pivot table or a chart but you'll have to figure out how to set them properly for your task.

  6. Hi,
    I am trying to write a formula and was hoping someone may be able to help.
    I have created a training matrix for my work.
    Column A is the name of each employee, and Column B is labeled (Safety Training) Column C is labeled (ESD Training). Under each column I place an X next if the person in column A has been trained.

    On a second sheet, I am making a page for (Safety Training). I want that page to list each name that has an X under the safety training column on Sheet 1.

    I have created this formula =IF(Matrix!T6="X", Matrix!A6) which will bring the names into sheet 2, but In a list of 10 names, I see the names in column A on sheet 2, but the word "FALSE" elsewhere.

    Is there a way to look at sheet 1, and take just the names in Column A, that also have an X in Column T and list them on Sheet 2?
    I hope all of this makes sense.
    Thanks for the help.

    1. Hi Dan,

      Your formula looks correct, so it's hard to tell why it doesn't work without seeing your data.

      I should also mention that it is VLOOKUP that is usually used to match and pull records from one table to another. We also feature a special tool that returns all matches based on your conditions. Feel free to check it out.

  7. Hi,
    I'm trying to write a formula that will give me a text statement in column B if column A contains a specific number.
    For instance Column A can have a number that is 1-24, each number has a different item assigned to it. What I would like to do is put in the specific number in column A and have column B auto populate with the item name that correlates to it.
    1 = Apple
    2 = Orange
    7 = Pumpkin
    So on and so forth.
    Is there a formula that will actually do this?

    1. Hi Ricky,

      Sure. Assuming your numbers start from A2 and end in A10, here's the formula for numbers you specified:
      =ARRAYFORMULA(IF(A2:A10=1,"Apple",IF(A2:A10=2,"Orange",IF(A2:A10=7,"Pumpkin"))))

  8. =IF(and(B2="Wellness Pod","70")IF(and(B2="45min Pod Roll","47.40")))

    B2 column is a drop menu that we select which service the client had.

    I then need the value or a percentage of that value to display in the total column.

    Please help.

    (Also this is in google sheets)

    1. Hello Jessie,

      If I'm getting it clear, your formula should look like this:
      =IF(B2="Wellness Pod",70,IF(B2="45min Pod Roll",47.4,IF(...)))

  9. I'm trying to do a function where if F2, G2, or H2 are yes then I2:M2 are 0 but if F2, G2, or H2 are no then it's the autosum of I2:M2. I know this can be done in excel but the computer I have I am not able to put excel on. Can anyone please help me write this out to where it works or tell me if it can even be done?

    1. Hello Tawana,

      I guess this is a formula you need:
      =IF(OR(F2="yes",G2="yes",H2="yes"),0,SUM(I2:M2))

      However, you can't enter it to I2:M2 since these cells should be counted if any of F2, G2, or H2 have "yes" in them.
      So in other words, either the formula should be entered to other cells or you need to adjust the conditions.

  10. If(N2,N2,if(O2,$P$1,""))
    IN THIS FORMULA,EVERYTHING IS PERFECT. BUT THE PROBLEM IS THAT WHEN I ENTERED A GOOGLE FORM SHEET AND IT APPEAR RESULT ON GOOGLE SHEET. THE FORMULA AUTOMATICALLY SHIFTED INTO NEXT CELL.
    PLEASE HELP ME: JUST TELL ME HOW I CAN ALLOCATE FORMULA EACH AND EVERY CELL IF ANOTHER INFO. IS ENTERED FROM GOOGLE FORM LATER...

    1. Hello Sayra,

      I'm afraid you will have to copy the formula manually since each submitted Form response is added as a new row to Google Sheets.

  11. i want to permit serially typing to 50 staff members in excel(google spread sheer) eg. 1.ABC, 2.DEF, 3.GHI, 4.JKL, in this serial other than 1 will not type data before 1st NO., then only No. 2 will type data, then only 3, then only 4 ---will type the data by using e-mail

    1. Hello there,

      I'm really sorry but your task is not clear to me. Please describe it in more detail and provide an example of what you have at the start and what you're trying to get.
      I'll try my best to help.

  12. Principal:Interest:Balance:
    $330.29 $69.71 $23,465.76
    $83.32 $131.68 $23,382.44
    $587.18 $27.82 $22,795.26
    $606.29 $8.71 $22,188.97
    $536.55 $78.45 $21,652.42
    $0.00 $21,652.42
    I'm wanting the spreadsheet to show "$0.00" if there is no principal or interest is added in the row. How would I do the formula to make it show this? =SUM(K10-I11) is what the formula is looking like!

    1. Hello Mike,

      I'm sorry I'm a bit confused. Is it the balance that should show $0.00 if principal and interest are missing or something else? What do you use the SUM formula for?
      Please provide more details on your task so I could understand it fully and suggest accordingly.

      Thank you.

  13. I pickup cars in different zones and each zone has a different pay rate. Is there an if formula to calculate each zone and pay rate on one sheet?

    1. Hello Scott,

      You can either build a nested IF and specify each zone and its pay rate there (they can be listed in an additional table and simply referenced in the formula) or use IFS instead.

  14. how can I input the value from the cell in a formula? for instance
    =If($B2="Campbell", substitute($H2," [ ", " Campbell "),$H2)
    The value of B2 is Campbell but the value of C2 is another name. So rather than have to type the name manually or pasting it into the formula, how can I input the value from any cell into the formula above?

    1. Hello JB,

      I'm sorry, can you please provide examples of what is exactly in C2 (since you're looking for a square bracket) and what you need to have as a result of the formula?

  15. Hi,
    i wanted to make due date format based on three criteria. I wanted a texts on a specific cell like "Processing" if date on other cell is later than 7 days from today, "Coming soon" if date is within next 7 days from today and "Pending" is date is earlier than today. i used below formula but only for "coming son " criteria not working properly. cell shows "coming soon" if the date is one day earlier than today. others criteria working properly.

    Formula used:
    =IFS(I2<TODAY(),"Pending",I2TODAY()+7,"Processing")

    Please help me on that.

    1. Hi Munna,

      You need to use AND for the "Coming soon" criteria:
      AND(I2>=TODAY(),I2<TODAY()+7),"Coming Soon"

      I'd recommend adding it for "Pending" as well to avoid getting it if I2 is empty:
      AND(I2<TODAY(),I2<>""),"Pending")

  16. Hi, I'm trying to create a cell that will show if the date in one cell is "outdated" or "updated" and a blank return if there is no date listed on that specific cell. I have already create the first part, but I'm having a hard time in introducing the "BLANK" part. I'm new in this kind of stuff and still exploring. Hope you could help.
    Thank you!

    1. Hi Eana,

      Use a pair of double quotes ("") to check if a cell is blank (=IF(A2="",...)) or to return a blank cell if the condition is met (=IF(A2=20,"YES",""))

  17. Hello, I am trying to make a sheet that connects to a form. The form will be asking what tools are needed, if they put needed i want it to put a 1 in the column and if its not needed than put a 0. How would i formulate that? i tried =if(B4 "needed", "1", "0") but that did not work.

    TIA!

    1. Hello Madison,

      You missed an equal sign after the cell reference. Also, numbers don't need to be in quotes. Please try this formula:
      =IF(B4="needed",1,0)

  18. Hi there, I am trying to create a ticket tracking system that will display pricing based on location and date. I have 5 locations named "1J", "3A", "4Y", "10Y", and "ND" that are listed using Data Validation in column C, and Dates in November are Listed in Column A. I would like to have the price populate in column B based on the Location selected AND if the date listed in column A is before 11/18/19 the price would show one amount, and if the same location was selected but it was after 11/18/19 then it show a different amount. Example: If "1J" was selected on 11/5/19 the price would be "$20", If "1J" was selected on 11/19/19 the price would be "$22" because 11/19/19 is after 11/18/18. I want cell B2 to change if the date is before 11/18/19 and a certain location has been chosen in cell C2. So far this formula works but how would I create a formula that allows for the other 4 locations to possibly be selected and location relevant pricing populate?
    Also FYI 11/18/19=43787
    =IF(AND(A2<43787,C2= "1J"), "$18", "$20")

    1. Hello,

      If I understand your task correctly and the prices vary not only based on dates but also on locations, you need to create a nested IF and list each price for each set of conditions, for example:
      =IF(AND(C2="1J",A2<43787),"$18",IF(AND(C2="1J",A2>=43787),"$20",IF(AND(C2="3A",A2<43787),...))))

  19. Hello, I am trying to create an inventory tracker to track multiple items being sold at multiple locations. I have an Inventory sheet that has Product A listed (lets call that A2) and then a numerical quantity under Original Stock at Location A (B2). Then there's Current Stock at Location A (C2). Then I have a Sales sheet. What I'd like is when a sale is recorded for Product A at Location A, for C2 on the Inventory sheet to equal (B2- the new sale). I was able to make an IF formula that works when the sale is recorded in a specific cell, but the sales will be listed in chronological order so I'm trying to make the formula work for the entire column all the way down. Is there any way to do that? Thanks so much!

    1. Hello Jamie,

      I'm really sorry but the description is a bit confusing. Please share a small sample spreadsheet (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. The email address above is for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

      I'll look into it and do my best do advise you.

  20. I have a column that will a yes or no. Based on the answer, I would like for another column to calculate a tax. Example, if cell F13 is Yes, then I would like to look at an amount in cell G13 and calculate the tax based on the amount in cell G13. Then if cell F13 is No, I would like for it to look in cell G13 and calculate the tax based on the amount in cell G13. Bottom line, if a person is married (Yes) then there will be one calculation and if a person is not married (No) there will be a different calculation.

    1. Hello Robert,

      If I'm getting your task clear, a formula like this will do:
      =IF(F13="Yes",calculation_for_YES_with_G13,calculation_for_NO_with_G13)

      In case you're still not sure how it works, please describe how your data is arranged in more detail.

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

    1. Hello Nadeem,

      I'm sorry but the formula you provided can't return TBD in case it's already in A1. A cell with this formula will remain empty in this case.
      I kindly ask you to double-check the formula in your spreadsheet and the contents of A1.

  22. I'm doing of spread sheet of our household bills and want to show the difference from the previous month with some result being + change some being - change. How do i do that?

  23. Hello,
    I'm trying to create a fixed daily summary of the data below.
    Date Fruit Name Quantity
    4-Nov-19 Apple 10
    4-Nov-19 Orange 20
    4-Nov-19 Grapes 50
    4-Nov-19 Banana 15
    5-Nov-19 Apple 20
    5-Nov-19 Orange 50
    6-Nov-19 Orange 20
    6-Nov-19 Grapes 50
    6-Nov-19 Banana 15
    This below is the summary format I want to do
    Summary
    Date Apple Orange Grapes Banana
    4-Nov-19
    5-Nov-19
    6-Nov-19
    ...

    1. Hello,

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

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

  24. I am looking for help to create a formula that will calculate tax liability as follows:
    1.) If annual income is less than $42,900 then tax is calculated by multiplying the tax amount by 5.8%
    2.) If annual income is $42,900 or greater and is less than $101,550 then tax is calculated by multiplying the difference between $42,900 and the annual income by 6.5% then adding $2,488.
    3.) If annual income is greater than $101,500 then tax is calculated by multiplying the difference between $101,550 and the annual income by 7.15% and adding $6,447
    Any assistance would be helpful!

    1. Hello Christie,

      Please check if the formula below works as desired. It is written supposing that your tax amount is in column A and annual income is in column B, so just make the necessary adjustments based on your data:
      =IF(B2<42900,A2*5.8%,IF(AND(B2>=42900,B2<101550),(B2-42900)*6.5%+2488,IF(B2>101500,(B2-101550)*7.15%+6447)))

  25. I am trying to add an IF statement that would add a multiplier if they play for a certain team. So like, I have rows of stats, name and team. So IF they play for the rockies they would have a .9375 multiplier. However the thing I need it multiplied to is already a formula. So can I add an if statement to a formula, if so, how?

    1. Hello Jackson,

      For me to be able to help you out, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      Note. That email for file sharing only. Please do not email there. Once you share the file, just confirm by replying here.

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

      1. Is it possible to do this but searching for a value in a column?
        I would like that if A1 = value in any cell in a column B, put cell C in the same row in cell D in same row.
        Hope it makes scense.

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

    1. Hello Aditya,

      Using the formula, you can only make it return a webpage for the user. Just enter the hyperlink as an argument, and it will appear if value=true. However, the person will have to click the link in order to open it.

  28. If I want to use this function on existing data, and if the predicate is false I do not want the row to change, how do I go about that?

  29. =IF(B2>=96.01%,"5",if(B2<96%,"4",if(B2<95%,3,IF(B2<94%,2,IF(B2<92%,1)))))
    Help on this please.
    1 96.01%

  30. I'm trying to populate a certain text in column F if column c says a specific thing. I'm using =IF(C3:C237 = "DMM901Y", "No PPW Status Available", " ") on the F column, but it's not working. All help is appreciated. Thank you!

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

      1. Thank you for your question, Lindsay.

        Try wrapping your IF with an ARRAUFORMULA:
        =ARRAYFORMULA(IF(C3:C237="DMM901Y","No PPW Status Available",""))

        If you're wondering how it works, please check this blog post.

  31. MILES RATE TOTAL
    34 1.88 $366.22

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

    1. Hello Patrick,

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

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

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

        1. Hi Ben,

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

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

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

    1. Thank you for your feedback, Laura.

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

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

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

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

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

    1. Alison,

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

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

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

    1. Daniel,

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

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

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

    1. Hi Missy,

      I believe Fran above had a similar question. Please have a look.
      If Vlookup doesn't help you, please consider sharing a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      I'll look into it and try to help.

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

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

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

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

    1. Robyn,

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

      1. Ahh, Thank you Natalia ! :)

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

    1. Fran,

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

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

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

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

    1. EF,

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

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

  41. Hi,

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

    1. Hi Lee,

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

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

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

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

    1. Annie,

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

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

    1. Hi Susan,

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

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

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

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

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

    1. Jeff,

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

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

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

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

    1. Mark,

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

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

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

    1. William,

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

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

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

    1. Thank you for another explanation, Nikola.

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

      1. Nikola,

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

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

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

  50. Hello, some help here?

    1. Hello Nikola,

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

      1. Thank you!
        Looking for your response.

        1. Nikola,

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

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

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

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