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

  1. Thank you so much for this article! It's just what I was looking for.

    Is there a way to set a condition for if one cell contains certain text, then the number in the cell next to it (on the same row) is populated into a 3rd cell. I'm trying to sort a list of transactions into their budgeted buckets. Column A is the expense amount, Column B is which bucket the expense is budgeted in. I want to format other cells so that whenever Column B says "Bucket 1" the dollar value in Column A of that same row is inserted into these cells.

    Thank you!

    1. Hello Dean,

      Thanks for the feedback!

      If I understand your task correctly, you need to put he formula into your 3rd cell saying:
      =IF(B2="Bucket",A2,"")

      At the end of this part of the article, you'll find a way to process the entire column with one formula at once.

      1. Thank you for the response!

  2. I am trying to combine a number from one cell, and some text into a second cell. For example, B2 has the number 6, and then I want B3 to say "6 out of 10". How do I get it to take the value from B2 and then add the "out of 10"?

  3. Hi,
    Thanks for the information. Your articles are all so helpful. I have a question. How do I do an IF statement for if a number is within a certain range. Let's say A1 may have any number from 0 to 20, and I need a formula that has one result based on the value in A1 being from 0 to 4, another result on 5-11, and another on 12-20. I'd rather not have to write out nested IF statements for every value from 0 to 20.

  4. Hello!

    If amount in each row is more than 10, the points should be added 1000 for each row. How to use formula in google sheet?

    Example;
    Row 1 col 1=10 attendance, row 2 col 2=45 attendance, row 3 col 3=25 attendance. The points to auto calculate should be 3000 points. how to auto calculate this? because sometimes the attendance could be lower than 10 which no points for it.

  5. This was so helpful. I spent most of my day searching how to do something and you had the answer. You guys are awesome !!

  6. Hello! I am currently trying to create a formula that will pull names from column C on a sheet titled Comm Log who have 3 numbers below 60 in 3 different columns. I have started with: =IF(Comm Log!M3<60,Comm Log!C3,"") but I am pretty stumped. Any help would be appreciated!

    Thank you!

    1. Hello Carl,

      If I understand your task correctly, you need to incorporate the AND function to your IF formula. It will look at 3 different columns for the required number at the same time.

      You can also try our IF Formula Builder add-on. It lets you enter your conditions the user-friendly way and will gather everything into a formula for you.

  7. guys, i'm not that technical to this but i can get a few things done...

    i'm trying to get say,

    column

    if
    column A is 1 and B is 1 then C is value of money, say £100 and then
    which means i can also mate it
    if A is 0 and B is 1 then C is £50

    is there a way to give value to 1 and to 0 and maybe 2 ?

    and what could be the best formula for it ?

  8. How to use below functions in google sheet

    1. =IF(Values_Entered,-PMT(Interest_Rate/12,Loan_Years*12,Loan_Amount),"")
    2.=IF(Values_Entered,Loan_Years*12,"")
    3.=IF(Values_Entered,Number_of_Payments,"")
    4=IF(Values_Entered,SUMIF(Beg_Bal,">0",Extra_Pay),"")
    5=IF(Values_Entered,SUMIF(Beg_Bal,">0",Int),"")
    6=IF(Values_Entered,1,"")
    7=IF(Values_Entered,Loan_Amount,"")
    8=IF(Pay_Num"",Scheduled_Monthly_Payment,"")
    9=IF(Pay_Num"",Scheduled_Extra_Payments,"")
    10=IF(Pay_Num"",Sched_Pay+Extra_Pay,"")
    11=IF(Pay_Num"",Total_Pay-Int,"")
    12=IF(Pay_Num"",Beg_Bal*Interest_Rate/12,"")
    13=IF(Pay_Num"",Beg_Bal-Princ,"")

    1. Hello Praba,

      I'm not sure I understand your question. Do you get any errors when entering these formulas to your Google sheet?

  9. Hi,
    I can't work out which function(s) I need!
    I want to create a pie chart that collates percentages by category. I have books listed by genre, with their page count as a percentage of the total page count of all the books in the sheet. How do I group the percentages by genre to make a pie chart?
    E.g.
    Genre (Fantasy), % of total pages 6.21%
    Genre (Fantasy), % of total pages 4.62%

    How would I get a pie chart to show "Genre (Fantasy), 10.83%"?

    I thought I would need to use the IF function to get it to add up the percentages but I'm really unsure. Any advice would be amazing!!

    1. Hi Leeza,

      Yoy may want to check these special blog posts for the solution:
      Google Sheets percentage formulas
      How to make a Pie Chart in a Google spreadsheet

      If you're still unsure how to prepare the data and make the chart, for me to be able to help you better, please share 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. 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 do my best to help you.

    2. Oops, I meant to add- I've tried using the SUMIF function (=SUMIF(e3:e999, ”fantasy”, h3:h999)) but I get an error.

  10. I am trying to highlight the rest of the columns once I checked the checkbox. Is that an IF function?

    Thanks in advance!

  11. Hello!
    I'm trying to use a "simple" IF-function, but if the value is "false" I want the cell to remain empty. Or is their a better function for this?

    The values in the cells is "JA" or "NEJ". If "JA" = Recycle, IF "NEJ" = nothing.
    "=IF(L14="JA";"Recycle")"

      1. Thank you so much! You saved my day =)

  12. Hi can you help me, I am using the following two "IF" functions.
    IF(F21=24, 0%, 18% )
    IF(F21=24, 9%, )

    If I enter "the F21 cell" value manually it works. But if I automate the value of "the F21 cell", let's just say I fill "the F21 cell" with help of the VLOOKUP function or data validation, then "the IF function" is not working at all. Please enlighten me about my mistake.

    1. Hi Vasav,

      I'm not sure what's happening on your side, everything works as expected on mine.

      For me to be able to help you, please consider sharing your problem spreadsheet with us: 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 to this comment.

      I'll try to help.

      1. As per your instruction, I have shared the Sheet with a given account. For instruction plz refer to Raw 51. I have tried to explain the problem and also showed you result I am expecting.

        1. Thank you for sharing your spreadsheet, Vasav.

          All your numbers in the TIN column in the Statewise GSTIN Code sheet appear as text. Hence, your VLOOKUP in F21 returns text values. But the conditions in your IF formulas are for numbers, that's why they don't work. Because F21 contains text.

          Change records in the TIN column to numbers and everything should work correctly then.

  13. Im trying to add 60.00 to string when the answer is True under PCR Test but the string wont add. The rest is adding correctly. Thanks in advance.

    =if(E2>14,E2*7,if(E214,E2*7,if(E2<14,P1+100,if(H2="True",P1*60)+(F2*2)+(G2*5))) (Nothing adds or formulates) ?

    1. Hello Shawn,

      Please consider sharing an editable copy of your spreadsheet with us: support@apps4gs.com. Please include a 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). 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 it.

  14. Good Day!

    This is such an awesome elucidated If-Function usage! I would like to ask if would it be possible to combine this IF-fucunction and the CHAR (=CHAR) function such as 10004 and 10008?

    For example, if the If statement is true, the cell will yield =CHAR(10004). Else its=CHAR(10008)

    Thank you very much!

      1. Wow! Thank you!

  15. Hello

    I want multiple cells with text in all to equal 1 and then be added together eg

    Cell 1. Cell 2. Cell 3 Cell4
    g. a z. 3

    Are you able to help?

    thanks

    Wayne

    1. Hello Wayne,

      I'm sorry but your task is not clear to me. Please describe it in detail and provide some examples of the data you have and want to get.

  16. Greetings,

    I'm trying to evaluate if a cell has a specific string, then run the REGEXEXTRACT() function to get everything after the string as the TRUE return value, else for the FALSE return value just return the value of the cell itself:

    =IF(SEARCH(“~”, D2) > 0, REGEXEXTRACT(D2,".*~(.*)"), D2)

    This just gives me an error. Is there a way to do this in Google Sheets? Seems a simple thing.

    1. I will say that the REGEXEXTRACT is working just fine IF the tilde (~) is present. Otherwise, it returns #N/A

      1. Hi Jonathan,

        You're getting #N/A because SEARCH still return 1 for some reason and REGEXEXTRACT don't see matches for your regular expression. As Google Docs for SEARCH suggests, it's recommended to use something like IFERROR to check for cases when there aren't matches to the search. So your formula need to look like this:
        =IFERROR(IF(SEARCH("~", D2)>0,REGEXEXTRACT(D2,".*~(.*)")),D2)

  17. Hi. We have to build a spreadsheet on google sheet to track sales invoices with invoice amount, paid amount, outstanding balance stating the status if it is already past due or if it is a current amount. thank you.

    1. Hi,

      Well it sounds like the IF function from this article will do for your task. Have you already tried it?

  18. I would like to enter a number and have a functioning formula that subs a percentage from same cell's input and gives an output in the same cell

    1. Hello Amalia,

      I'm afraid you can't enter formulas into the same cells that you're referring to with the formulas themselves. Google Sheets just doesn't work this way. You will have to enter formulas in another column.

  19. I was trying to link the IF function to a cell with a drop down. The goal is to get the cell with the IF function to display the corresponding price for an item when selected from the dropdown.

    It didn't seem to work when I tried the formula in multiple formats. Any help would be appreciated.

    1. Hello Tyler,

      Please provide the exact formula you're trying to use.
      Also, please specify what you mean by "formula in multiple formats".

  20. Help would be appreciated! I have min/max level of inventory that I have to maintain and I have an Inventory sheet and an order sheet. I want the Order Sheet to pull data from the Inventory Sheet if the on hand number is equal to or lower than the minimum level that is set for that item.

    I know I will need to =FILTER('Inventory Sheet'!$A$2:$G$784,???

    TIA

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

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

  23. =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).

  24. is it possible if formula builder without google sheet

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

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

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

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

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

    select malaysia = 17% /
    select singapore = 16%

    please help

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

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

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

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

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

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

  36. DOH logic flow

    5 & 7 & 10 day- Priority P4

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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