Google Sheets IF function - usage and formula examples

The IF function in Google Sheets is one of the easiest functions to learn, and while this holds true, it is also a very helpful one.

In this tutorial, I invite you to take a closer look at how Google Spreadsheet IF function works and what advantages you will get from using it.

What is the IF function in Google Sheets?

Whenever you use the IF function, you create a decision tree in which certain action follows under one condition, and if that condition is not met – another action follows.

For this purpose, the condition of the function must be in a format of the alternative question with only two possible answers: "yes" and "no".

This is what a decision tree may look like: Decision tree of the alternative question.

So, the IF function allows you to ask a question and indicate two alternative actions depending on the received answer. This question and the alternative actions are known as three arguments of the function.

IF function syntax in Google Sheets

The syntax for the IF function and its arguments are as follows:

=IF(logical_expression, value_if_true, value_if_false)
  • logical_expression – (required) a value or logical expression that is tested to see if it is TRUE or FALSE.
  • value_if_true – (required) the operation that is carried out if the test is TRUE.
  • value_if_false – (optional) the operation that is carried out if the test is FALSE.

Let's explore the arguments of our IF function in more detail.

The first argument represents a logical question. Google Sheets answers this question with either "yes" or "no", i.e. "true" or "false".

How to formulate the question properly, you may wonder? To do that, you can write a logical expression using such helpful symbols (or comparison operators) as "=", ">", "<", ">=", "<=", "<>". Let us try and ask such a question together.

Usage of the IF function

Let's assume that you are working in the company selling chocolate in several consumer regions with many clients.

This is what your sales data may look like in Google Sheets: Sample sales data for Google Sheets.

Imagine that you need to separate sales made in your local regions from those from abroad. To accomplish that, you should add another descriptive field for each sale – a country where the sales took place. Since there is a lot of data, you need this description field to be created automatically for each entry.

And this is when the IF function comes to play. Let's add the "Country" column to the data table. "West" region represents local sales (Our Country), while the rest are the sales from abroad (Rest of the World).

How to write out the function properly?

Place the cursor in F2 to make the cell active and type in the equality sign (=). Google Sheets will immediately understand that you are going to enter a formula. That's why right after you type the letter "i" it will prompt you to choose a function that begins with that same letter. And you should choose "IF". Function prompt in Google Sheets.

After that, all your actions will be accompanied by prompts as well.

For the first argument of the IF function, enter B2="West". As with the other Google Sheets functions, you don't need to enter the address of the cell manually – a mouse click is enough. Then enter comma (,) and specify the second argument.

The second argument is a value that F2 will return if the condition is met. In this case, it will be the text "Our Country".

And again, after the comma, write the value of the 3rd argument. F2 will return this value if the condition is not met: "Rest of the World". Do not forget to finish your formula entry by closing parenthesis ")" and pressing "Enter".

Your entire formula should look like this:

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

If everything is correct, F2 will return the text "Our Country": Google Sheets IF function.

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

Tip. There's one way to process the entire column with one formula. The ARRAYFORMULA function will help you do that. Using it in the first cell of the column, you can test all cells below against the same condition, and return the corresponding result to each row at the same time:

=ARRAYFORMULA(IF(B2:B69="West","Our Country","Rest of the World")) Process entire ranges using IF + ArrayFormula.

Let's examine the other ways of working with the IF function.

IF function and text values

The usage of the IF function with a text has already been illustrated in the example above.

Note. If the text is being used as the argument, then it must be enclosed in double-quotes.

IF function and numerical values

You can use numbers for the arguments just as you did with the text.

However, what is very important here is that the IF function makes it possible to not only fill cells with certain numbers based on the conditions met but also calculate.

For example, let's say you offer your clients various discounts based on the total value of the purchase. If the total is more than 200, then the client gets a 10% discount.

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

=IF(E2>200,E2*0.1,0) IF with numbers in Google Sheets.

IF blanks/non-blanks

There are cases when your result depends on whether the cell is empty or not. There are two ways to check that:

  1. Use the ISBLANK function.

    For example, the following formula checks if cells in column E are empty. If so, no discount should be applied, otherwise, it's 5% off:

    =IF(ISBLANK(E2)=TRUE,0,0.05) Check if a cell is blank in Google Sheets.

    Note. If there's a zero-length string in a cell (returned by some formula), the ISBLANK function will result in FALSE.

    Here is another formula to check if E2 is empty:

    =IF(ISBLANK(E2)2<>FALSE,0,0.05)

    You can turn the formula the other way around and see if cells are not blank instead:

    =IF(ISBLANK(E2)=FALSE,0.05,0

    =IF(ISBLANK(E2)<>TRUE,0.05,0)

  2. Use standard comparison operators with a pair of double-quotes:

    Note. This method considers zero-length strings (indicated by double-quotes) as empty cells.

    =IF(E2="",0,0.05) – check if E2 is blank

    =IF(E2<>"",0,0.05) – check if E2 is not empty.

    Tip. In a similar manner, use double-quotes as an argument to return an empty cell by the formula:

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

IF in combination with other functions

As you have already learned, the text, numbers, and formulas can act as the arguments of the IF function. However, other functions can play that role as well. Let's see how it works.

Google Sheets IF OR

Remember the first way you figured out the country where you sold chocolate? You checked if B2 contained "West".

However, you can build the logic the other way around: list all the possible regions that belong to the "Rest of the World" and check if at least one of them appears in the cell. The OR function in the first argument will help you do that:

=OR(logical_expression1, [logical_expression2, ...])
  • logical_expression1 – (required) the first logical value to check for.
  • logical_expression2 – (optional) the next logical value to check for.
  • and so on.

As you can see, you just enter as many logical expressions as you need to check and the function searches if one of them is true.

To apply this knowledge to the table with sales, mention all the regions that belong to the sales abroad, and the other sales will automatically become local:

=IF(OR(B2="East",B2="South"),"Rest of the World","Our Country") IF with OR in Google Sheets.

Google Sheets IF AND

The AND function is just as simple. The only difference is that it checks if all listed logical expressions are true:

=AND(logical_expression1, [logical_expression2, ...])

E.g. you need to narrow the search to your town and you know that it is currently buying only hazelnuts. So there are two conditions to consider: region – "West" and product – "Chocolate Hazelnut":

=IF(AND(B2="West",C2="Chocolate Hazelnut"),"Our Country","Rest of the World") Google Sheets IF AND formula.

Nested IF formula vs. IFS function for Google Sheets

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

Let's assume that you have set stricter discount conditions for your clients. If the total purchase is more than 200 units, they get a 10% discount; if the total purchase is between 100 and 199, the discount is 5%. If the total purchase is lower than 100, there is no discount whatsoever.

The following formula shows how the function will look in the cell G2:

=IF(E2>200,E2*0.1,IF(E2>100,E2*0.05,0)) Google Sheets nested IF.

Note that it is another IF function that is used as the second argument. In such cases, the decision tree is as follows: Nested IF decision tree.

Let's make it even more fun and complicate the task. Imagine that you're offering the discounted price to the one region only - "East".

To do that correctly, add the logical expression "AND" to our function. The formula will then look the following way:

=IF(AND(B2="East",E2>200),E2*0.1,IF(AND(B2="East",E2>100),E2*0.05,0)) Using AND in Google Sheets nested IF.

As you can see, the number of discounts has reduced greatly while their amount remains intact.

There's also an easier way to write the above thanks to the IFS function:

=IFS(condition1, value1, [condition2, value2, …])
  • condition1 – (required) is the logical expression you want to test.
  • value1 – (required) is the value to return if the condition1 is true.
  • and then you just list conditions with their values to return if they are true.

Here's how the above formula will look with IFS:

=IFS(AND(B2="East",E2>200),E2*0.1,AND(B2="East",E2>100),E2*0.05) Use IFS instead of nested IF.

Tip. If there's no true condition, the formula will return the #N/A error. To avoid that, wrap your formula with IFERROR:

=IFERROR(IFS(AND(B2="East",E2>200),E2*0.1,AND(B2="East",E2>100),E2*0.05),0) Avoid errors with IFERROR.

SWITCH as an alternative to multiple IFs

There's one more function you may want to consider instead of the nested IF: Google Sheets SWITCH.

It checks if your expression corresponds to a list of cases, one by one. When it does, the function returns a corresponding value.

=SWITCH(expression, case1, value1, [case2, value2, ...], [default])
  • expression is any cell reference, or a range of cells, or even an actual math expression, or even a text that you'd like to equal to your cases (or test against the criteria). Required.
  • case1 is your first criteria to check the expression against. Required.
  • value1 is a record to return if the case1 criterion is the same as your expression. Required.
  • case2, value2 repeat as many times as criteria you have to check and values to return. Optional.
  • default is also completely optional. Use it to see a specific record if none of the cases is met. I'd recommend using it every time to avoid errors when your expression doesn't meet matches among all the cases.

Here are a couple of examples.

To test your cells against a text, use ranges as an expression:

=ARRAYFORMULA(SWITCH(B2:B69,"West","Our Country","Rest of the World")) Use a range as an expression to test against the text.

In this formula, SWITCH checks what record is in every cell in column B. If it's West, the formula says Our Country, otherwise, Rest of the World. ArrayFormula makes it possible to process the entire column at once.

To work with calculations, it's better to use a boolean expression:

=SWITCH(TRUE,$E2>200,$E2*0.1,AND($E2<200,$E2>100),$E2*0.05,0) Use booleans as an expression to test against calculations.

Here SWITCH checks if the result of the equation is TRUE or FALSE. When it's TRUE (like if E2 is really greater than 200), I get a corresponding result. If none of the cases in the list is TRUE (meaning they are FALSE), the formula simply returns 0.

Note. SWITCH doesn't know how to calculate the entire range at once, so no ARRAYFORMULA in this case.

IF statements based on a count

One of the questions we get asked a lot is how to create the IF formula that will return whatever you need if the column contains or doesn't contain a certain record.

For example, check if a customer's name appears more than once in a list (column A) and put the corresponding word (yes/no) into a cell.

A solution is simpler than you may think. You need to introduce the COUNTIF function to your IF:

=IF(COUNTIF($A$2:$A$69,$A2)>1,"yes","no") Use COUNTIF within your IF function.

Make Google Sheets build IF formulas for you – IF Formula Builder add-on

If you're tired of keeping track of all those extra characters and proper syntax in formulas, there's another solution available.

IF Formula Builder add-on for Google Sheets offers a visual way of creating IF statements. The tool will handle syntax, extra functions and all required characters for you.

All you need to do is:

  • fill blanks with your records one by one. No special treatment for dates, time, etc. Enter them as you always do and the add-on will recognize the data type.
  • select required comparison operators from the suggested drop-down lists.
  • if needed, add multiple logical expressions in a click: IF OR, IF AND, ELSE IF, THEN IF.
IF Formula Builder add-on for Google Sheets.

As you can see, each logical expression takes its own line. The same goes for true/false outcomes. This reduces the number of possible confusion over the formula drastically.

As you fill everything out, the formula for use will grow in the preview area at the top of the window. To its left, you can select a cell in your sheet where you'd like to have the formula.

When you're ready, paste the formula into the cell of interest by clicking the Insert formula button at the bottom.

Please visit the online tutorial for IF Formula Builder to see all options described in detail.

I hope that there's no room for any doubt now that the IF function, though a very simple one at first glance, opens the door to many options for data processing in Google Sheets. But if you still have questions, feel free to ask them in the comments section down below – we'll be happy to help!

You may also be interested in

Table of contents

601 comments

  1. I am trying to use data ranges to calculate mutiple levels of water usage and the price per tier. Can you help?

    Input
    Monthly Gallon Usage 132,455

    Output
    Monthly Bill $2,705.64

    Monthly Bill Calculator
    Lower Limit Upper Limit Usage in Tier Per Thousand Rate Charge in Tier
    - 0 3,000 3,000 $63.35 $63.35
    3,000 5,000 2,000 $17.57 $35.14
    5,000 11,000 6,000 $15.71 $94.26
    11,000 26,000 15,000 $14.41 $216.15
    26,000 101,000 75,000 $12.98 $973.50
    101,000 1,000,000,000 106,455 $12.43 $1,323.24

    • Hello Jon,

      I'm sorry, your task is not clear. For me to be able to help you better, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) including the example of the expected result. 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 sent it.

  2. Hello! I'm working on a tracking document for inventory. The team sometimes does audits so I have to take into account a potential 0 or blank space when asking the formula to grab the information from one sheet into the next. There are three columns I'm using, A, B, C. This will pull information from a previous sheet in columns B & C and populate column A in a new sheet.

    A is the start of day numbers
    B is the end of day numbers based on calculations in columns D-F
    C is the audit column which will sometimes be 0/blank, and sometimes have numbers that will override the B number.

    I want to create a formula that auto-populates column A with whichever number from the day before is correct. The formula works like so:
    If C is blank or 0 then pull B, if C has a value above 0 pull C

    I've tried variations on this formula: =ifs('6/25/23'!L4="",'6/25/23'!K4,'6/25/23'!L4=0,'6/25/23'!K4,'6/25/23'!L4>0,'6/25/23'!L4)

  3. I'm looking to move answers from one sheet to another only if the question matches. So IF cell A matches cell B then move the answer over from cell C to cell D. But the questions might be in an whole column so I'd want it to check the whole column.

  4. I am trying to build a formula where if there are ANY numbers put in column E there is yes put in B, but if E is blank there is nothing in B

    • Hello steph,

      Try this formula (change the ranges to yours):
      =ARRAYFORMULA(IFERROR(IFS(ISNUMBER(A1:A10),"ENTER_NUMBER",A1:A10="",""),""))

  5. I am trying to build an if formula. Column B has total net Rev, Column C $ per work order.
    In column E I am looking for if $ per work order 44.99 and net rev>$500 it will multiply by .13
    Column I should be if $ per work order is >64.99 and Net Rev>$500 it will multiply by .15

    Also I would like it to say $0 if in colum G and I did not match the criteria

    • Hello Daniel,

      It looks like you need the AND criteria in your IF function. I explained it here. To build the formula with numbers correctly, please also look at the examples here.

  6. I am trying to sum the total of a column of if statements. Each cell in the column is as follows: =if(G61>0,"1","")
    I am trying to add the total of the produced column but can not seem to come up with a formula that will add this column up. I am guessing it is because Google sheets is using the results not as a number value. Any help would be appreciated. Their are 77 rows in the column and about 50 have a value of one but it is not totaling that.

  7. Is it possible to use the IF function for something like this: If the cell contains a sentence without a punctuation mark, that part of the cell will be removed?

  8. Hello, Im looking for a formula to calculate billable hours at different rates for different customers and it all depends on if its a billable service, and I'm not sure if the IF function is correct. my example is i need to bill customer A $50.00 per hour based on input hours if a check box is checked true and customer b $60.00 per hour again if check box is true, but leave the cell blank if the check box is false. Let me know if you can help.

    • Hello Stephen,

      If I understand your task correctly, IF will solve it. To see if the box is checked, you can literally use TRUE/FALSE:
      =IF(A2=TRUE,...,...)

      To leave the cell blank if FALSE (not checked), use a pair of double quotes like in the Tip here, step 2.

  9. How do i create an if statement that achieves the following.

    1. I have 1000 named accounts each with a unique physical address information.

    2 I would like to build a sheet where if an account name was input in a row in column A then column B of that same row will auto populate the correct address that corresponds with the account. This rule continues in the following rows below.

  10. HI,

    I am trying to create a formula that would put a specific amount when 3 different drop downs are in place...

    I have 3 drop downs : 1st drop down is union or non-union, 2nd drop down is dancer, assistant, choreographer, and 3rd drop down is full day or half day.

    I would like it to generate a cost depending on whichever array of drop down is in play... example: if I have union, assistant, and half day that would equal $1 or if I have non-union, dancer, full day that would equal $2 , and so forth.

    please help, ive been working on this for days to no avail. Thanks :)

  11. This seems like it should be an easy function. Every combo of formulas even with the formula builder won't grab the data I'm looking for. I have two sheets I'm working in. I'm trying to pull data from another sheet (JanExp) based on column criteria. My column criteria is "subscriptions". I want the value in Column E based on if its criteria is "subscriptions". The formula it's giving me seems to work, but it just gives me a blank value even when a number is present in column E, but it isn't giving me an error saying my formula is invalid.

    =IF(JanExp!C1 = "Subscriptions", JanExp!D1, )

    • Ha- GOT IT!! I figured it out. I needed a SUMIF in front of it. I knew it didn't seem that difficult. Thanks for the great reference point to get me started!

      =SUMIF(JanExp!C:C,"Subscriptions",JanExp!D:D)

  12. I have three columns in Google Sheets:
    Column P = Effective Date
    Column Q = End Date
    Column R = Days Remaining in Term

    In Column R, I want to show the number of days between the End Date (Column Q) and today's date. If Column Q is blank, I want the corresponding cell in Column R to be blank. If Column Q is a date in the past, or less than today's date, I want the corresponding cell in Column R to show "Past Due".

    Currently, I am using this formula: =if(and(isdate(P37),isdate(Q37)),DATEDIF(Sheet2!$A$1,Q:Q,"D"),) and it is working for the first two scenarios, however, I can't figure out how to account for the third scenario, when the cell should say "Past Due".

    • Hello Trystan,

      I'm sorry, I've got a bit confused by the Sheet2!A1 (does it contain the TODAY function?) and with the logic of the 'Past Due' label if the end date was before today's (it isn't past due then, is it?). Hence, for me to be able to help you better, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) including your formula.
      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 and try to help.

  13. hi i'm trying to use your examples with the following:
    column a (a drop down menu with three options)
    column b (numbers)

    I'm trying to create an if function that will calculate total of column B IF column a has a certain menu option.
    my formula is not working: =SUMIF((a10:B50="Foundation"),(b10:b50))

  14. Team, I am calculating whether employees have earned an hour of acrued hours based on hours worked. When they work at least 37 hours a week, they earn an hour of sick time up to 40 hours for the year. I created and if/and formula that populates a 1(representing an hour) or 0 if they have not worked 37 hours. In addition, once they reach forty hours of sick time and acrue 40 hours of sick time, the formula will stop acruing hours. I created an if/and statement in each of the cells. I need to total the # of YTD hours acrued. I have not been successful in finding the appropriate way to sum the column. I am assuming that is because of the formula in each of the cells.

    How can I sum a column of cells that have #'s achieved with an if/and formula.

      • Hi Natalia,
        Any help with Linda's formula? I am trying to do the same thing using a column when an employee hits 30 hours.

        Thank you!

        • Hi Joanne,

          I suggested Linda to try SUMIFS to tally time based on the condition provided. I'm unable to provide a formula since I don't have all the details necessary and don't know how the data is arranged. Have you seen this function?

    • Hi Linda,
      Can you give me the formula you used ? This is exactly what I need. Thank you in advance for any help!
      Joanne

  15. I am trying to create a simple function in a Google Sheet whereby a cell (D10) checks another cell (B10) for specific text (Hotel or Apartment)

    If it finds the text 'Hotel, it populates 'D10' with the content of 'E5', however if it finds the text 'Apartment', it populates 'D10' with the content of 'E6'

    This then needs to be repeated for the next 6 rows accordingly

    See screenshot https://prnt.sc/J8y9CPNTDuk5

    It seems like a very simple command but I am struggling to get it to work

    Any help would be greatly appreciated

  16. Hello! I'm trying to allocate a value to a reference with the "IF" formula, but it doesn't seem to work. To explain a bit further, I'm building a spreadsheet to help me calculate costs for dinners. I have a column with a list with 2 items. Based on the item selected (Menu 1 or Menu 2) I would like the next column to automatically add the allocated cost.

    For example, if I select Menu 1 in my column, I'd like the price to appear in the next column automatically.

    So far, I tried this one =IF(C3="Menu 1","39.50","49.50")

  17. Hello,
    I am trying to execute an if statement that will add up all the hours of an individual worker. I have a sheet where individual projects are recorded, with the date, the names of the workers who participated in the project and the hours they worked on the project. I would like the if statement to sum up all the values ​​in the hours column if there is an individual employee's name in the second column.
    I wrote the statement like this, but it doesn't work, it returns FALSE and I can't find a reason why it doesn't return sum of the hours.

    =IF(Gora!E2:E="Name of the worker";SUM(Gora!C2:C))

    Gora is the name of the sheet that contains information about the projects, since I do the totals of hours on another sheet.
    I can easily create such if statements in Excel, so I hope that they can also be used in Google Sheets.

    Thank you for your answer, Maša

  18. writing a macro and want to test for a number or blank cell, cant find a function. "ISNUMBER()" does not seem to work in a macro and I cant find the equivalent in the macro.

  19. Column A = 10,000
    In Column B, if column A is equal or less than 90,000 then the value in column B must be the value in A
    in Column B, if column A is greater than 90,000 then the value in column B must 90,000

  20. Hi,
    What to do if I want this scenario:
    Column A = 10,000

    in Column B = If column A is = 90,000, the value in column B must 90,000

  21. Hello,

    I'm making a budget spreadsheet and in column D I have spending categories, and in column E I want it to auto populate with Essential or Non-Essential based on the text in col. D. Example: "Groceries" and "Debt Payments" would be essential while everything else is non essential. I've tried variations of the IF THEN formula laid out above and read through all of the comments but in every variation of the formula I keep getting errors. So far I've only got two versions of this formula to work but it only has one spending category from col D included.

    =IF(D3="Debt Payments","Essential")

    =IF(D2="Debt Payments","Essential","Non Essential")

    To include more spending categories such as groceries, I've tried this:

    =IF((D4="Groceries","Essential");(D4="Debt Payments","Essential"))

    This formula says a parse error. I've also tried

    =IF(OR(D4="Groceries","Essential");(D4="Debt Payments","Essential")) and the same error happens. The same happens when I tried this

    =IF((D4="Groceries","Essential","Non Essential"),(D4="Debt Payments","Essential","Non Essential"))

    If I can't have col E populate and note that all other spending categories are non essential thats fine.

    Thank you in advance for your help!

    • I've tried this formula on multiple lines of the spreadsheet, thats why the cell number kept changing above.

      • Figured it out

        =IF(OR(D3="Debt Payments", D3="Groceries", D3="Mortgage"), "Essential","Non Essential")

  22. Hi there

    I need a formula:

    If A1 is blank, doesn't allow B2 to enter a value.

    Thanks in advance

    • Hi Shafiq,

      You need to use Data validation for this.
      Go to Data > Data validation. As a cell range, pick B2. As a criteria – the following custom formula: =A1<>""
      Pick Reject input on invalid data and hit Save.

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

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

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

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

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

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

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

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

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

    • Hello Praba,

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

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

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

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

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

    Thanks in advance!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    • Hello Tyler,

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

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

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

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

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

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

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

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

  46. is it possible if formula builder without google sheet

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

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

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

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

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

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

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

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