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

  1. Hello
    I want to know if it is possible an IF Function in Google Sheets in the following way -

    I have an excel file with Project Topics corresponding with numbers counted 1 to 40 that are completely anonymous to all the students. I want to apply an IF function in such a way that - once a student enters a number from 1 to 40 in the Google Sheet, the Project Topic corresponding with that number in my excel file be displayed in a separate column in the Google Sheet.

    This way I can assign Projects, assignments and everything randomly to the students so that they dont feel like I am being biased towards any students while doing so. Another way by which it can help me is that sometimes students start fighting with each other over topics if I let them choose topics on their own out of the list.

    I think it will help a lot of other teachers as well if u can solve this problem. Thanks alot !

    1. I forgot to mention a couple of things if u are wondering about it.
      1. If students enter the same Number between 1 to 40 more than once, I dont have a problem in that coz Students must themselves see that the number is already taken up by someone.
      2. All the Students must be able to see the topics that have been assigned on basis of this Google Sheet and my Excel Sheet.

  2. Hello,
    My "If" formula is only checking the line where is it writted.
    Ex: I write in A1 "=IF(A5="plop", "good", "bad")" It works, because A1 and A5 are in the same line
    But if I write in A1 "=IF(B5="plop", "good", "bad")" it affiche me #VALUES!

    Any help? thank you

    1. Hello Jozzy,

      IF doesn't really care where it's written. In your case, I'd recommend looking at B5 – what does it contain? If you hover your mouse over the #VALUE! error in A1, what does it say?

  3. =if(B12>800, sum(b12 to G12), if (B12<800, sum(E12 TO G12)))

    I encountered something like above formula. I reproduced above from recollection and cannot refer back to that file.

    The only point I want to ask is if B12 has only text, should not the result of above formula be an error?

    Why the result is a number which is addition of E12 TO G12.

    Thanks

    1. Hello Prav,

      You get the result as a second calculation because any number (even negative) in Google Sheets is less than the letter 'a'. I can't say why it works this way but you can check that by simply trying and comparing 'a' to '-10,000', for instance. That's why you'd better use numbers in cells where the logic for your formulas requires numbers.

      If you think this shouldn't work this way, you can leave feedback to the Google Sheets team directly (Help - Help Sheets improve via the menu).

  4. is it possible if formula builder without google sheet

  5. hi,
    i wanted to write a formula for if a certain cell is not blank then it will show a hyperlink but if not it will remain blank

    i tried making my own formula

    =IF(ISBLANK(A2),=HYPERLINK("https://api.whatsapp.com/send?phone="&A9&"&text="&B9,"send whatsapp msg"),)

    is there in changes or alternative to implement?

    1. Hi Dhruv,

      You messed with a syntax a bit, here's a correct formula:
      =IF(ISBLANK(A2),HYPERLINK("https://api.whatsapp.com/send?phone="&A9&"&text="&B9),"send whatsapp msg")

  6. Hey! I'm struggling to work out how to write an IF equation which I feel is super basic! I'm wondering if anyone could help me out?

    All I'm wanting is for a cell to turn red if the input is either above 0.3 or below 0.3. Eg: 5.8 = green, 6.1 = red.. It seems like it would be so basic but my attempts have failed

  7. HI can you help pls, I have sheet of overtime : col A with names, col B dates, col C start time, col D end time, Col E (D-C) total hours worked on single entry in HH:MM, Col F overtime rate (either 10% or 20%).
    Names may be repeated as it's a daily list over a month, so may total 30+ rows listed in date order

    I want to create a linked monthly summary table which will show:

    new col A (manually entered): each name once in alphabetical order (employee list)

    in new col B : cumulative total of hours worked by a single matched individual name + macthed at 10% rate (to match conditions looking at rows A & F - if both conditions met then add that hh:mm time in E to a sum

    in new col C : cumulative total of hours worked by a single matched individual name + at 20% rate (to match conditions looking at rows A & F - if both conditions met then add that hh:mm time to a sum total)

    Struggling with the if / then aspect for the sum if selected conditions are met in 2 data range- know this is wrong, tried multiple variations.
    =IF(COUNTIF($A$2:$A$30,"NAME")>0,AND,COUNTIF($F$2:$F$30,"10%")>0)=SUM($E$2:$E$30)

    Any Help or advice ? Thanks

    1. Hi Dominic,

      For me to be able to help you better, please share an editable copy of your spreadsheet with us: support@apps4gs.com. Specify where your formula is and add a second sheet with the result you expect to get. The result sheet is of great importance and often gives us a better understanding than any text description.

      If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and try to help.

  8. Team, Could you help me with the Google sheet formula to get the counts of Text using logical function.
    Example:
    If(Text=" Company"(Coulumn 1), Countif("Data Base",! 'Active')+countif("Database", 'Inactive')-Different column combined needs to get the result

    1. Hello Gopi,

      Sorry but your task is unclear. Please consider sharing a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance and often gives us a better understanding than any text description. I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and try to help.

  9. DROP BOX = MALAYSIA,SINGAPORE,FILIPINE
    how.

    select malaysia = 17% /
    select singapore = 16%

    please help

  10. Hi, can you help me how to make a formula that based on the covered period time it will call the window:

    1st window
    4-8PM
    2nd window
    8-12MN
    3rd window
    12MN-4AM
    4th window
    4-8AM
    5th window
    08-12NN
    6th window
    12-4PM

    Thanks.

    1. Hi Russle,

      If I understand your task correctly, you should use the Apps script to call for windows. We're always ready to help you, but we do not cover the programming area (script-related questions). You may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

  11. HELLO,

    PLEASE HELP

    IF A CELL CONTAINS 50% OR ABOVE THEN CLASS 1 OR BELOW 50% THEN ECE.
    FORMULA IN CELL A2
    CONDITION IN CELL A1

  12. Hello,

    Can you let me know where i am going wrong. it says i have only imputed 1 argument.

    =IF(OR(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EjfESGzrXaeh6oaWrZWMGOuCEzgdgFQpO6pzEMZxpJc/edit#gid=1328129828&range=J5:J66", "bennys tune sheet.!J5:j66")="YES",(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EjfESGzrXaeh6oaWrZWMGOuCEzgdgFQpO6pzEMZxpJc/edit#gid=1328129828&range=J5:J66", "bennys tune sheet.!J5:j66")="NO"),"YES", "NO"))

    Many thanks in advance, and thank you all for the amazing guide :D

    1. Hello Alex,

      There are a couple of excess brackets. Here's a correct formula:
      =IF(OR(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EjfESGzrXaeh6oaWrZWMGOuCEzgdgFQpO6pzEMZxpJc/edit#gid=1328129828&range=J5:J66", "bennys tune sheet.!J5:j66")="YES",IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EjfESGzrXaeh6oaWrZWMGOuCEzgdgFQpO6pzEMZxpJc/edit#gid=1328129828&range=J5:J66", "bennys tune sheet.!J5:j66")="NO"),"YES","NO")

      BTW, have you seen our IF Formula Builder from the last part of the article? It worries about the correct syntax for you so I'd suggest you check it out :)

  13. Hi! and thanks for a great article, very helpful. I am hoping you can help me with what is possibly a very basic function - I want to create a signal between the change of two numbers. For example, if on Tuesday the number in cell A1 is 10 and on Wednesday the number in cell A2 is 5, I would like a trigger cell to tell me that there has been 50% change from one day to the next.

    Can you please help me with this? Thanks so much!

    Patrick

  14. Hi I am looking at using the IFs function however, I want it to return a cell from another sheet or be able to return a true statement that has skipped lines if you know what I mean IE

    in A1 = Apples and I want my True statement to appear as:
    B1 Red
    Apple
    Sauce
    Rocks

    instead of Red Apple Sauce Rocks?

    1. Hi Sarah,

      you can either press Alt+Enter when you type the formula to wrap the words,
      or insert line breaks using a true statement like this: "Red"&CHAR(10)&"Apple"&CHAR(10)&"Sause"&CHAR(10)&"Rocks"

  15. Related text need to display in 1st table from the 2nd table, If we write a remarks in the 2nd table following and related text in the 1st table -- can any one write the if formula for this

    1. Hello Suresh,

      I'm afraid there's not enough info to provide you with a formula. Please describe your task in detail so we could help.

  16. DOH logic flow

    5 & 7 & 10 day- Priority P4

    How can I run this logic in google sheet? Please help on urgent note.

  17. I am using a simple IF statement to populate a numerical value. I then use that value for another calculation and a SUM at the bottom the section.

    It's not reading the number as a number in the SUM function, but it is in the other cell.

    I've also tired saving and opening in Excel.

    I'd so appreciate suggestions, it's making me crazy LOL.

    Analyze request 2 1
    Is the data in Personas? Yes =IF(H4 = "yes", "2", "8") =I4/3
    Is the destination in Self Service? No =IF(H5 = "yes", "0", "8") =I5/3
    Requirements LOE Estimate =SUM(I3:I5) =SUM(J3:J5)

    Analyze request 2 1
    Is the data in Personas? Yes 2 1
    Is the destination in Self Service? No 8 3
    Requirements LOE Estimate 2 4

    1. Hello Rebecca,

      When you put numbers in the IF formula in double quotes, the formula returns them as text because this is how text is written in formulas. To have numbers as numbers, remove those double quotes from around your numerics, like this:
      =IF(H4="yes", 2, 8)

  18. how would i do an if statement like "if there is nothing in the cell above, move everything to cell above?" Is it possible to do that?

    1. Hello Eletron,

      I'm afraid you won't be able to do that with formulas. You see, formulas return results into cells where they are entered. Also, you can't enter the formula into a cell and check that cell for any condition at the same time. In other words, if you enter the formula to B2 and check if B1 is empty, the result will be returned to B2.
      What's more, you cannot cut-paste data using formulas, only copy-paste it. So any data joined from other cells in B2 will also remain in all other cells.

      1. oh ok

  19. Is there a function that would be true if the cell of interest contains any of the words in a specified column? I was going to use an IFS statement to tag rows based on what they contain.

  20. I'm trying to have a cell automatically round up no matter if the value is less than .5 or not. I need the value to round up always. So for instance if the value is 2.35, I need it to round up to 3.
    Thank you!

  21. So I'm trying to use a pretty basic IF OR formula in Google Sheets: =IF(OR(I299),"I2","").

    The trick is that for my value if true, I want to use a cell reference, not just a static value / word. When I use just the IF statement without OR, it accepts I2 as a cell reference, but when I make it an IF OR it no longer recognized my "value if true" as a cell reference.

    Is there anyway to adjust that?

    1. Hello Natalie,

      If you want I2 to be treated as a cell reference, you should omit double-quotes. Double-quotes make values text strings.
      Also, OR requires some condition, not just a cell reference, and is usually used to list several conditions to see if at least one of them is true. Hence, the correct formula for you should look like this:
      =IF(I299=SOMETHING,I2,"")

      If that doesn't really work for you, please describe your task in detail and I'll help with the whole formula.

  22. if the two column cells are red in colour then write 0.5 in another column how to write a formula can you please tell any one

    1. Hello Sethu,

      There are no standard formulas that would check cell colors. We have a special tool for that: Function by color. You can use it to count colored cells. However, it can’t return the value you need based on colors. You will need to build the simplest IF formula afterwards to return 0.5 if our add-on returns 2.

  23. Hi everyone,

    I've read thru many comments but still not understanding exactly how to approach my specific calculation need.

    My need is specific to the Canadian Recovery Benefit, which has a cap of 18K for my situation. I am allowed to make up to 38K in salary/profit without penalty or obligation to reimburse any portion of the 18K. But for every dollar made over 38K that I make (with the exlusion of the 18K govt benefit) I would need to reimburse half of the benefit received (0.50) until I reach the threshold where I am obligated to reimburse all of it (which if my calc is correct, is 74K total salary).

    ie. If I make 38K or less, and also receive the 18K govt benefit, I am not obligated to reimburse any of the benefit. I am naturally obligated to pay the income tax on the 56K total.

    If I make 48K (exluding the govt benefit), I am obligated to reimburse 5K of the 18K govt benefit received. 0.50 cents per every dollar of the 10K I am over the limit.

    If I make 74K (excluding the govt benefit), I am obligated to reimburse the full 18K govt benefit. (74K being the threshold of 38K, plus 2X the 18K maximum benefit)...

    I'm trying to generate a sheet in which I can enter the amount of money I have made to date into one cell, and have the sheet tell me 1) If I am over the limit - this calc is easy, and, 2) tell me how much I need to reimburse in the even that I make of over the 38K.

    I am assuing this is not that hard, but the formulas for it are above me.

    Here's as far as I got:
    =IF(I41=">$38,000.00","No Pay", "Pay") this would simply alert me if I had crossed the threshold and needed to pay. Its not very elegant, but for the first part seems to work.

    But what formula (or addition to the original) would tell me how much of cell I41 would be payable if it crossed the 38K...? Iaking into account the .50 of every 1.00 more made... I hope this makese sense.

    Any help or tips to making this work are greatly appreciated. Thank you!
    Bob

      1. Hi Bob,

        Thank you for the detailed description and the screenshot. If I understand your task correctly, the following formula should do the trick:
        =IF(I41<=38000,"No Pay",IF(AND(I41>38000,I41<74000),(I41-38000)/2,18000))

  24. Hi

    I am trying to populate a yearly expense dashboard I created. I have sheets for every month. In column B (expense category "Grocery") and column D ($ amount)

    I am trying to add all values in Column D ($ amount) that have "Grocery "in column B to populate a monthly total for groceries on my dashboard.

    I am tired of sorting the column by category and redoing the formula to calculate totals every few week.

    Is this possible?

    1. Hi Natan,

      I'm sorry I'm afraid I need more details on your task to be able to suggest anything.

      Please create a small sample spreadsheet with (1) a couple of sheets with the example data and (2) with a sheet showing the result you want to get. Then share this file with us: support@apps4gs.com. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.
      I'll look into it and see what may help.

      1. Hi I have sent this.

        1. Hi Natan,

          Thank you for sharing a sample. You need to use the SUMIF function for the task. I entered the correct formulas to the first 3 columns on the Dashboard sheet so you could see what to do. If you need more details on the SUMIF function, we have a cool blog post about it, feel free to look through :)

  25. I am working with schedules, for Example: 0300-1100,1100-1900,1900-0300 three different shifts, How can I make all 0300-1100 scheduled employee names appear in a group?

    Thank you

    1. Hello Jake,

      Please create a small sample spreadsheet with 2 sheets: (1) example data, (2) the result you want to get, and share it with us: support@apps4gs.com. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.
      I'll look into it and try to come up with a solution.

      1. Hi Natalia,
        I just shared the spreadsheet to that email. Thank you.

        1. Hi Jake,

          I've got the file, thank you.
          Please look at B16, B19, D16 and D19. I used QUERY to return the required records. You just need to create similar formulas for other days.
          The same can be done (with and without formulas) with our Multiple VLOOKUP Matches add-on.

          Also, B3:I12 contained some excess spaces that prevented from getting the correct results. I trimmed them with our Remove spaces tool.

          Hope this helps!

  26. Want to set True if cell contains a specific word. How to do that?
    for ex: Let's say cell F4="Rest of the world". Then, if "Rest" contains in the cell F4, set True, else False.

  27. I am trying to create a IF formula where IF cell P2 = Y then "Need to be invoiced" is entered into Q2, however, if R2 has a date inputted into it, Q2 will have "Invoiced" into it. I am having trouble as I get one or the other to work but not together.

      1. That formula worked with the exception when P2 is yes but there is no date in R2, the Need to be invoiced did not populate. do I need either an OR or And function in there?

        1. Nevermind - I figured it out! P2 uses the word "Yes" not just a "Y" as soon as I fixed that it worked.
          Thank you for your help!

  28. Hi, I am looking for a formula to help with our staff holiday tracking sheet. We have a new policy where holiday entitlement run from Birthday to birthday or each staff (we used to run the from 01/01 to 31/12) As it is our first year doing this it will have run from 01/01 until their birthday this year and then start again. I need a formula that will: one, pull through the start date of the holiday year and the end date and two, change date after the birthday has passed this year. I also then need one that will calculate how man holiday days are accrued between the start and end date of the holiday year.
    E2 - Staff name
    E3 - Holiday year start date (Need formula for this)
    E4 - Holiday year end date (Need formula for this)
    E5 - Holiday entitlement (need formula)

    A separate page will have all names and birthdays.
    Sheet name is Bdays - A1 has the bday date in (12/02/2021)

    Any help would be much appreciated. Im unsure if i need a Vlookup, or IF or maybe both?

    1. Hi Michaela,

      Do you intend to use a drop-down list of names in E2 and want other cells to change accordingly or you'd prefer having each staff member's info displayed at the same time?

      For me to understand your task better, please share a small sample spreadsheet with us (support@apps4gs.com) with an example of your data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account 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 try to come up with a solution.

  29. I created an order form for my craft shows and want the prices to populate when i select each item. I figure its an If:Then type of situation but Its been a long time since I've worked on things like this and could use some guidance

    Column B= Item Description
    Column D= Price

    I made a drop down list for column B to show all the items I currently have in stock. I also have a drop down list in Column D and can manually click on each price. I would love to figure out the proper way to bypass this.

    Example: Items- lollipop and chocolate are $1 each. bubbles, reindeer food, lip balm, and hand sanitizer are $3 each.

    Do I need to say something like If B=lollipop or chocolate then D=$1???

  30. How do i make the cell that has the formula in it blank until it populates? at the moment it has FALSE until the other cell is written in ?

    1. Hello Tania,

      Use a pair of double quotes as an argument to indicate a blank cell, like this:
      =IF(A2="West","Yes","")

  31. I would like to total the prices (Column D) of rows assigned in Column G. I have Data Validation to make a dropdown of values "1-4" (column G). I want to sum the values of all group 1 column D's, all group 2 column D's, etc to another cell.

  32. Could you please help me with a formula,
    IF the cell is EE then the number is 3, if the cell is ME then the number is 2, if the cell is NW , then the number is 1.

    1. Hello Rogelio,

      Assuming your cell is A2, here's a formula you can use:
      =IF(A2="EE",3,IF(A2="ME",2,IF(A2="NW",1,"")))

  33. My business focuses on retail, contractor, wholesale sales, but also includes stock. We do batch ordering and a majority of what we order is already pre purchased. I am trying to separate in one column a letter for each: R, C, W are what we are using as our definers. We want a count from each grouping, and also a percentage. Please help....

  34. If I want to past a formula into consecutive vertical cells but want to keep ONE of the sections of the formula the same, is this possible? When I paste it, the L30 goes to L31 then to L32, etc., and the D1 goes to D2 to D3, etc. I'm wanting the L to stay at 30 and the rest to move with each line.

    e.g., If I paste =IF(L30>=12.5,D1*25%,D1*20%) in A1, and =IF(L31>=12.5,D2*25%,D2*20%) in A2, and =IF(L32>=12.5,D3*25%,D3*20%), etc., etc.

      1. That is great! Thank you!

  35. Hello Natalia, I'm need to have multiple IFS and am having trouble with the formula. I need a cell to populate a dollar amount based on a percentage range in another cell. If the percentage is between 11 - 11.99 = $125, 12 - 12.99 = $250, 13 - 13.99 = $375, 14 - 14.99 = $500, 15 or more = $750. How can I write this formula?

    Thanks

    1. Hello Kevin,

      When it comes to IFS, you simply need to alternate your criteria with its result. The example is illustrated here.

      Assuming the percentage starts in A2, here's how the formula should look like:
      =IFS(AND(A2>=11,A2<=11.99),125,AND(A2>=12,A2<=12.99),250,AND...,etc,etc)

      1. PERFECT! Thank you!!!

  36. I'm trying to black out cells if the cell prior has N/A from a drop down list. This would also need applied to the entire column.
    Thank you in advance!

    1. Hi Kim,

      I'm sorry but your task is not clear.
      For us to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) and include two tabs there: 1 - your source data, 2 - the result you need to get.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. Thank you.

    1. Hello William,

      Though your formula works on my side, I'd adjusted it a bit:
      =ARRAYFORMULA(IF($J4:$J="name",$H4:$H*0.85,IF($J4:$J="","",0)))

      ArrayFormula auto-populates the entire column with the formula while the second IF returns an empty cell for those rows where column H is not yet filled with data.

      I tried to look into your file, but I can't access it. If you still need my assistance with the formula, please share an editable copy of your spreadsheet with us directly: press the Share button at the upper right corner of your spreadsheet and enter support@apps4gs.com.
      Note. We keep that Google account 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. Thank you.

  38. I'm trying to make a formula but I can't seem to find the answer to what I try to achieve.
    What i'm trying is to automatically subtract a percentage on an array when the cell next to it contains a name.
    =IFERROR(IF($J4:$J = "name", $H4:$H*0.85, $H4:$H*0))
    So if a cell in array J contains a "name, the cell left of where the name is substracts 15% That my formula is wrong, is for sure! But where? Thank you in advanced.

  39. Hi,
    figured it out finally!
    =IF(O$5>=$H6,if(O$5<=$I6,$D6,""),"")
    Patience and time solves everything!

  40. Hi,
    This is for a construction project...looking to put how many people will be on a job for each day. Using Google sheets....
    I have the dates in the main cell as follows:
    P5 is the date in question 5/26/2020
    H6 is the start date 5/26/2020
    I6 is the end date of the project. 11/9/2020
    once i run this formula i can make all cells (for this job) a specific color Perfect, and starts on 5/26/2020 stops on 11/9/2020.... :)
    =AND(O$5>=$H6,O$5=$H6,P$5<=$I6,D6*1,"")
    this does give a blank for the false but how do i get the true statement to read the D6 cell?
    Is there a way to get this number into each cell for the job by not typing it in. Seem so simple yet i am at a loss.
    any help would greatly appreciated
    Thank you

  41. Hi,
    I was wondering if I could receive some help with a formula. I'm trying to figure out how to create a scenario where I can view the commission rate (it differs by company) by company?
    It would be such that column B (where the companies are) would dictate the percentage of commission that's taken, and the percentage is taken from the sum of columns K-M. I want to create a commissions column in column N, and would like for the formula to run the length of the column.
    The % taken is such that Company B has 33% taken, Company IT, S-Corp, and AA has 28% taken, and every other company has 25% taken. I've linked the sheet below for easier viewing:
    https://drive.google.com/file/d/1SZ3U1SXiorYnsTt9566ymPWQDX734CY_/view?usp=sharing
    Thank you!

    1. Hi Amanda,

      Thank you for the description and the file.

      To calculate the commission rate by company, use this formula in B7 and copy it down the column:
      =IF(B7="","",IF(B7="B",33%,IF(OR(B7="IT",B7="S-Corp",B7="AA"),28%,25%)))

      To deduct the percentage from the total of columns K-M at the same time, here's another formula:
      =IF(B7="","",IF(B7="B",SUM(K7:M7)-33%,IF(OR(B7="IT",B7="S-Corp",B7="AA"),SUM(K7:M7)-28%,SUM(K7:M7)-25%)))

  42. Hello,
    Can you please help?
    I would like a formula to do the following. I have three columns. Column 1 are a list of dates, Column 2 is also a list of dates, Column 3 is a list of number. I want a sum of column 3 for only the following cells. Cell 1 has a value and Cell 2 does not have a Value.

  43. I've used an IF statement to populate a column with a '1' for a true value and a '0' for a false value. In another cell, I want to sum all of the '1' values but the sum formula isn't picking them up. Is it possible to sum up all of the 1 values returned from all of my IF statements?

    1. Hello Harry,

      Can you please share the exact formula you're using to sum those numbers returned by IF?

      1. Hi Natalia,
        I managed to resolve myself thanks. The problem was I has quotation marks around the true and false values which was stopping them displaying as values that could be summed.
        My original statement was =IF("B3=Test","1","0")
        And changing it to =IF("B3=Test",1,0) has fixed it.

        1. Hi Harry,
          Thank you for the update. Good to know you managed to fix the formula!
          Double quotes are used for text strings. When used with numbers, they turn numerics into the text and prevent them from being calculated.

  44. Hi
    I am trying to pick shipments from a list whch has columns with several information. I try to fix a correct formula which gives me the result 1 or 0, or True or False or a colour or whatsoever.
    Lets say I want the in Cloumn T see the result if either one of the conditions are fullfilled. Aka if one of the conditions is fullfilled should the result be 1/True/Green cell, etc.
    First condition: IF C3<=3,99 and D3<=999
    2nd condition: IF G3="Yes" and E3<=3
    3rd condition: IF C3=6 and D36 and D3>1000 and D3<2500
    I do not know what I am doing wrong, but I don't get it together, and with only 2 conditions (which are probably wrong) I get 1 as a result for a row with "yes" in cloumn G, whereas if the data in the column is "no", but it fullfills i.e. the first condition, the result is instead of 1, true? Appreciate your help so much. Thank you!

    1. Hi
      I forgot the conditions in the 2nd condition
      which is if G3 s "yes" and E3<=3 is kind of missing the part, that if E3=1 should D3<=800, but in case E3=2 should D3<=1600 or in case E3<=3 so should D3<=2400
      how is it possible to combine all the conditions in a formula?
      Sorry for the unconvinience.
      Looking forward to your reply
      Kindest regards

      1. Hi Fatos,

        I'm sorry but it's hard to get a clear understanding of what you're trying to achieve.
        For me to be able to advise you anything, please consider sharing a small example spreadsheet with us (support@apps4gs.com) with 2 sheets: a copy of your source data and the result you expect to get. Please include the formula that doesn't work as well and shorten the tables to 10-20 rows.
        Note. We keep that account 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.

        In the meantime, please look through the last part of the blog post more closely. It describes how to enter multiple conditions to your formula.

        1. Hello

          I've shared the file now

          thanks again!

          1. Hello Fatos,

            I'm sorry, I can see no files shared by you. Please follow these steps to share the file correctly:

            1. Create an example spreadsheet in Google Sheets. Make sure there are two sheets: 1 - example data with the formula that doesn't work; 2 - the result you expect to get. Please shorten the tables to 10-20 rows.
            2. Press the Share button at the upper right corner of the Google spreadsheet.
            3. Enter support@apps4gs.com there and click Done.

            The file will then appear in the Shared section of our Drive. For more details on sharing the files, please turn to this help page. Thank you.

            1. Hi there
              1 - I've shared as requested a simple version of the file. hope it worked this time

              2- does anybody know how I can create an automated list of tabs/sheets in the same worksheet. Its a schoolproject; a (daily) spreadsheet with over 70 tabs/sheets and we need to have a summary-tab (we need to "analyze" data for a period of 120 days, and have no knowledge or info on coding etc)

              All kind of help is greatly appreciated
              Thank you!

              1. Thank you for all your help !
                Have a great day...
                Fatos

              2. Hi,

                Thank you again.
                Re my second question; I need to get a list of the tabs in another (summary tab) tab of the same spreadsheet. Like an index, which I want to use to create a table and "extract and summarize" information, so the names of these tabs are going to be the rows in this table. I was not able to find the funtion to use for this

                Kindest regards

              3. Hi Fatos,

                I looked into your file. I created an additional column Q, named it Ablebits, pasted the formula there, and copied it down the column. Here it is:
                =IFERROR(IFS(AND(M2<=2,4;K2<1000);1;AND(AND(M2>2,99;M2<=6);K2<=50);2;AND(M2="pall";AND(N2>0;N2<=3);K2/N2<=800);3);0)

                The formula returns 0 instead of the error when there's no match to any of your conditions. Also, the column cells turned green due to the existing conditional formatting, but I'm not going mess with that. Please adjust your conditional formatting accordingly.:) Hope the formula solves the first task for you.

                As for the second one, I appreciate the additional info. But I'm afraid there's no easy way to avoid entering each sheet name manually. I'd advise you to search for a solution here - an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview
                I wish I could help you better.

            2. Hi,
              so sorry, I'll try to send it again. Sorry for the unconvenience... In the meantime did I manage to find I formula which appears to work. But I am not sure if that is the best way to do it. I'll try to send it now

              Thanks a lot & sorry again

        2. Hi Natalia

          Thank you sooo much, I will send you an exempel
          Kind regards

  45. I have a sheet where data copies from a master tab to a slave where I want some information removed or hidden as it will be shared outside our business. What I would like to achieve is IF it reads (in this case) 4S then the information shows on that second tab, and IF it reads anything else, for example CL, RN or EC then it does not automatically show on the second tab. This is all very new to me so would appreciate if you could advise how I go about this...

    1. Hello Bronwyn,

      If I get your task correctly, you should pull data using the QUERY function since you can add conditions for pulling there. I explained how it works in this blog post.

  46. I am trying to utilize Google Sheets to take a list of email addresses and see if it has ever been seen before.

    Emails are listed in Colum C and my string is:

    =IF(C2="C2;C1000","YES","NO") however it is returning NO no matter if it has been seen before.

  47. Hi, I need help correcting this formula which refers to a VLOOKUP before an IF command with an "and" condition:

    =IF(VLOOKUP(B69,$B$25:$C$43,2,FALSE)10000,C2560000,1800)))

    1. Hi Therese,

      I believe, you just miss the comparison before 10000. If you're looking if the match equals to 10000, it should be:
      VLOOKUP(B69,$B$25:$C$43,2,FALSE) = 10000
      Replace the equal sign with whatever comparison character suits your case.
      Also, there are two excess closing brackets at the very end of the formula.

  48. Most helpful and clear explanation on the internet. I seem to have encountered a problem. The cell I'm referencing has drop-down selector. The value of that cell from my view is whatever value has been selected. But the true statement never returns as true but only as false. =IF(E7="Business Analyst", "BA", "Nope") that only ever returns "Nope" despite that Business Analyst is selected and visible in the cell. Will this just not work or is there some other magic?

    1. Hello Christopher,

      Thank you for your lovely feedback!
      Please make sure the values in your Data validation are written exactly the same as you put them into your IF formula. I mean you should keep the same text case, spacing, etc. If you're still unable to make it work, please consider sharing a small sample spreadsheet with us (support@apps4gs.com) where the problem occurs. I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account 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 to help.

  49. Hello, I'm trying to condition a formula to pull info from two columns and then recognize which country it belongs to and from there show one number. So far I'm only able to pull from the first option "US" and if it pulls "CN" shows up as blank.
    =ARRAYFORMULA(IF(B5:B50="","",(IF (C5:C50="US",(Items!B2:B50),(IF(C5:C50="CN",(Items!C2:C50),(IF(C5:C50="UK",(Items!D2:D50)))))))))

    1. Hello Andie,

      I'm sorry but I'm a bit confused by the formula without seeing the data structure. Would you mind creating a small example of your data and share it with us (support@apps4gs.com)? I kindly ask you to shorten the tables to 10-20 rows. Please include your formula there as well. I'll look into it and see what may be wrong.

      Note. That account is for file-sharing only, please do not email there. Once you share the file, just confirm by replying here.

  50. Hello Natalia, thank you for your help
    I try to create a google sheet which picks up by name (drop down menu) a multiplication.
    example - if cell A2 = x then Cell B2*'Another Sheet'!A2' if cell A2 = y then B2*'Another Sheet'!A3' etc
    I have created a short formula with 3 items but I have 100+ items, might be an easier way or simpler? Would be a lengthy formula with a few brackets at the end :) :)
    My formula
    =IF(A2="x",B2*'Base Sheet'!B2,IF(A2="y",B2*'Base Sheet'!B3,IF(A2="z",B2*'Base Sheet'!B4)))
    Thanks in advance

    1. Hello,

      Try using IFS instead:
      =IFS(A2="x",B2*'Base Sheet'!B2,A2="y",B2*'Base Sheet'!B3,A2="z",B2*'Base Sheet'!B4,...,...)

      You will still have to list every condition and its outcome, but no nested IF is required.
      Alternatively, you could try building scripts, but we don't help with that. You may try to find a solution here - an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

      Hope it'll help.

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