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

607 comments

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

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

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

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

  3. Hi.
    How do I work out a formula if I want to charge 50p for every unit after 50 (the first 50 are free)?
    Thank you

  4. How would I nest more than three if statements in this formula?

    =if (h2 = 3560,44190),if(3570,44290),if(3580,43390),if(3590,43490),if(3600,43590),if(3610,43690),if(3620,43790),if(3630,43890),if(3640,43990),if(3650,44090),if(3660,44190),if(3670,44290),if(3680,44390),if(3690,44490))

    super simple replacement here but it errors after 3. not sure if I should be using a different method here.

    • Hello Joe,

      Here's how the correct syntaxis should look:
      =IF(H2=3560,44190,IF(H2=3570,44290,IF(H2=3580,43390,IF(...,IF(...)))))

      Or you can use the IFS function instead:
      =IFS(H2=3560,44190,H2=3570,44290,H2=3580,43390,...)

  5. Hello again Natalia,

    I have another formula question that is similar to what you helped me with last time. I am sharing the sheet with you now.

    What I am needing: I need a count of 1 or .5 next to "TOTAL =" depending on if their is a "SPLIT WITH" or not. If there is a persons name in the "SPLIT WITH" column then it needs to count ".5" and "1" if not.

    Thanks

    • Hello Kevin,

      Thank you for getting back in touch!
      I've got your file. I'll look into it and reply as soon as possible. Thank you for understanding.

    • Kevin,

      The formula is actually the same as before. You just need to change the ranges and remove one excess condition from the previous formula:
      =(COUNTIFS($C$3:$C$52,"<>"&"",$G$3:$G$52,"="&""))+((COUNTIFS($C$3:$C$52,"<>"&"",$G$3:$G$52,"<>"&""))/2)

      Copy and paste it to your E1.

      Though I'm using COUNTIFS (count based on multiple criteria), you can get to know how COUNTIF works (count by one criterion). The functions are similar and you will understand how these formulas work:
      COUNTIF in Google Sheets

  6. Hi,
    Let me explain the problem:column A is the asset of Jack & column B is the asset of Pot and column C is difference between A & B.

    =IF(A2>B2, "Jack has C2 more asset than PoT ","Pot has B2-A2 more asset than Jack").

    Is it correct???
    How to add both statement & cell value in same function.

  7. Hello there

    Hi I have a google sheet with guest check in and check out.
    my problem is I have the following function for timestamp when a value is entered in column A and gives me current timestamp in column C:
    //----
    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSheet();
    var r = ss.getActiveCell();
    //1.Change 'Sheet1' to be matching your sheet name
    if (r.getColumn() < 3 && ss.getName()=='Daily Guest Info') { // 2. If Edit is done in any column before Column (I) And sheet name is Sheet1 then:
    var celladdress ='C'+ r.getRowIndex()
    ss.getRange(celladdress).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");
    }
    };
    I also have a check box at K5 to be checked at the time of payment so I have a timestamp on J5 with a formula below entered in J5
    =if(K5,NOW(),"").

    However, if I already have a cheked button on K6 and have a timestamp in J6
    whenever I enter a value in A1 all the timestaps stored in J6 change together.
    I know this is caused by the formula =if(H5,NOW(),"")
    do you have a better formula or function for this issue

    Thanks Mack

    • Hello Mack,

      I'm sorry but NOW is the only function that returns a timestamp. It's a volatile function, so it recalculates itself in all cells each time anything is edited in the sheet. Thus, the standard formula is not an option here, I’m afraid. You need to use scripts to solve the task.

      Since we don't help with scripts, you may try to find the solution here – an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

      I wish I could help you better.

  8. hi , my condition is if in priority column value is "high" or "medium" AND in status coloumn value is "open", then "to do" must be printed else "hold" must be printed
    how to do this ?
    columns are priority(values="high","medium","low"),status(values="completed","open")

  9. Maybe someone can figure this out; I'm trying to modify a template I use to keep my checking balance by adding a column that can show me my balance based on what check have cleared, and a balance showing me what my actual balance is, including checks that may not have yet cleared. So, column D is a flag to tell me whether a check has cleared or not that I manually set. If that "x" is not true, or present, I'd like column H to show what is showing in the bank. Any suggestions are appreciated. https://docs.google.com/spreadsheets/d/1CDpexKUMuTcJEgCDXcq1XREFOoyjOsSyfBdISdiStNQ/edit#gid=0

    • Hello Michael,

      I looked into your file and adjusted the formula in column H for you based on how I understood your task. Try putting the below into H5 and then copy the formula down:
      =IF(D5="x", $G$4-SUM($E$5:E5)+SUM($F$5:F5), G4)

      If that's not really what you're trying to achieve, please describe the task and the desired calculations in more detail.

  10. My question is:

    I have two drop downs of salespeoples names (one in column K and column L) and I have another column (AF) that calculates how many times a salespersons name is chosen in column K. I want to know if there is a way that if "James" is used in column K and no name is in column L it produces a numerical count of 1 next to James' name in column AF. But if "James" is in K or L and "Kevin" is in the opposite one, can it produce a .5 for each person in column AF?

    • Hello Kevin,

      For us to get a better understanding of your case, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: a plain example of your data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      When sharing, make sure the option 'Notify people' is checked.

      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.

      We'll look into the file and try to help.

      • I added you to the spreadsheet in view mode only. As I stated, I have sales reps in Column K and L (sometimes). I want it to populate in column AF with the follow criteria:
        If a rep is in column K only then it counts 1 in column AF. If there is a rep in Column K and L then each rep gets a .5 in column AF.

        I hope this makes sense.

        • Thank you for sharing the file, Kevin.

          There's one thing I need to clarify. Should the formula give .5 to each occurrence of a rep in both columns in the same row only? (And at the same time give 1 to all other occurrences when there's no name in the same row in column L?)
          Or should it give .5 to all occurrences of a rep in column K if the same name appears anywhere in column L at all?

          • If their name is in Column K and there is no name in colomn L of the same row, then they get a 1. If their name is in either K or L with another name in the same row, then both reps get a .5 for that row. Each row should have either a 1 assigned or 2 - .5's assigned depending on an entity in column K or L. I hope that helps.

            • Kevin,

              Try this one in AF4 and copy it down the TOTAL column:
              =(COUNTIFS($K$3:$K,"="&$AE4,$L$3:$L,"="&""))+
              ((COUNTIFS($K$3:$K,"="&$AE4,$L$3:$L,"<>"&""))/2)+
              (COUNTIFS($L$3:$L,"="&$AE4,$K$3:$K,"<>"&"")/2)

              • Hmmm. That didn't work. It gave the rep a full count if they were in column L. On the sheet I shared with you, look at row 31. It has Aaron in column K and Adam in column L. In column AF it should give each of them a .5 count next to their names. In row 32 it has Evamarie in column K and no one in column L. This should give Evamarie a 1 count in AF next to her name. Let me know what I'm doing wrong....

              • Kevin,

                I'm sorry, I don't see the formula I provided in your sheet so I don't see the result you're getting.
                This formula checks both columns at the same time. If only K or L is filled on the row, the name gets 1. If both columns are filled, both names get 0.5. Then all these numbers are summed in your Total next to the name of the interest.

                If this is not what you expected - my apologies if I get something wrong from your description. I kindly ask you to create and share a small example table that would illustrate the exact result you're trying to get.

              • You know, It is EXTREMELY possible that it is my fault. Which quotations in your formula does that reps name go in?

      • Natalia,

        I just gave you edit permission. Please help, if you can.

        Thanks again

  11. Hi there thanks for the amazing article! it's helped a lot.
    I currently am having an issuewith my if + vlookup statements.
    My current string:
    =if(B1=5,=VLOOKUP(I1,data!A:D,2),VLOOKUP(I1,data!A:D,3))

    If B1=5 I want it to look up I1's data in Collumn 2
    If B1=6 I want it to look up I1's data in Collumn 3
    If B1=7 I want it to look up I1's data in Collumn 4

    example
    B1 I1 J1
    5 10 =look at number 10 Collumn 6 and give that back

    • Hi Caleb,

      If I'm getting your task clear, you should nest several IFs for multiple conditions like this:
      =IF(B1=5,VLOOKUP(I1,data!A:D,2),IF(B1=6,VLOOKUP(I1,data!A:D,3),IF(B1=7,VLOOKUP(I1,data!A:D,4))))

  12. I am trying to change the color based on a column with a dropdown to color a gantt. Any help would be great

    =IF(F4 ='Ministry', SPARKLINE(if(OR(today()D4),{int(C4)-int($C$2),if(C4=D4,1,int(D4)-int(C4))},{int(C4)-int($C$2),today()-int(C4),1,int(D4)-today()}),{"charttype","bar";"color1","white";"Light Blue"}),

    IF(F4 = 'Project',SPARKLINE(if(OR(today()D4),{int(C4)-int($C$2),if(C4=D4,1,int(D4)-int(C4))},{int(C4)-int($C$2),today()-int(C4),1,int(D4)-today()}), {"charttype","bar";"color1","white";"Light Blue"}),

    IF(F4 = 'Cerner/AscTech',SPARKLINE(if(OR(today()D4),{int(C4)-int($C$2),
    if(C4=D4,1,int(D4)-int(C4))},{int(C4)-int($C$2),today()-int(C4),1,int(D4)-today()}),{"charttype","bar";"color1","white";"Light Blue"}),)));"max",int($D$2)-int($C$2)}

  13. I am trying to use an if statement based on a data validation "yes" or "no" If the answer is yes the result will be a hyperlink (in a label) which the user can click to. If the the data validation is blank then the result is blank. If the answer is "no" I wish to return a text response. I am getting the hyperlink by getting the link from a different area in the sheet.
    Can anyone help as its driving me mad!

    • Hello Lloyd,

      Supposing your data validation is in A2, the link is in B2, the formula you need can be as simple as this:
      =IF(A2="yes",B2,IF(A2="no","your_text_response",""))

  14. I'm looking for a formula for this
    A B C
    1 Red 50 Lunch
    2 Black 72 Dinner
    3 Blue 83 Lunch
    4 Orange 17 Lunch
    5 Green 23 Dinner

    Totals
    8 Lunch
    9 Dinner

    I want the SUM of Lunches that is in column B to show in B8 (150) and the SUM of Dinners that is in column B to show in B9 (95). How would I write out the formula? This is just an example of what I want to do, it's really for different bills (A) that are in different categories (C). The amount is in column B. I basically want the B's to add up by category below in row 8 and 9.

  15. Hey,

    I am trying to design a grade calculator for my students but I am stuck!

    I have made a dropdown with YES or NO in one column for certain units of work, and I need the formula for if they put 'yes' in all boxes it needs to show distinction, if they put yes in some and no in others its a merit or pass (dependence on answers to yes and no's)

    Anyone have any answers? I have been googling for a while!

    Any help would be ace!

    • Hey Jacky,

      I'm afraid your task is not clear. With those little details you provided, I can only suggest how to start the formula: =IF(COUNTIF(A2:A10,"Yes")=9,...), where A2:A10 is a supposed column with the answers.
      What distinction should be seen and where if all answers are 'yes'? Also, "its a merit or pass (dependence on answers to yes and no’s)" please specify when it should be 'merit' or 'pass' exactly.

  16. Disregard previous question.

  17. Is there a way to write a function that keeps track of the sum of certain values in a column, if different column values meet a certain criteria into one cell.
    I have a chart to keep track of certain expenses for my building business.
    In one column it gives the "type of material" and then in another column it has the purchase amount. The type of materials change(ie: plumbing, framing, kitchen), so i need to get the sum of only certain values.
    Materials Type Purchase Amount
    Plumbing $20.00
    Trim $10.00
    Plumbing $20.00
    Is there a way on one cell to find the sum of only the plumbing total?

  18. Please disregard my previous email. A simple 'refresh' did the trick. Duh!

  19. Hi, I am a teacher and I'm trying to create a spreadsheet to track whether or not my students' test grades meet certain criteria.
    I have typed: =if(H7>=65,"yes","no)
    But when I hit Enter, the entire, exact formula is what shows up in the cell (not the 'yes' or 'no').

  20. Receiving a "VALUE" message when trying to add two if statements;
    D1 cell input; =SUM(IF(A2="x",E15,0)+IF(A3="x",E16,0); D1 displays "#VALUE"
    Cell References; E15=$40; E16=$20

    When separating the if statements into separate cells, values appear:
    D2 cell input; =IF(A2="x",E15,0); D2 Displays $40 value
    D3 cell input; =IF(A3="x",E16,0); D3 Displays $20 value

    D4 cell input; =SUM(D2+D3); D4 displays "#VALUE"

    Is Google unable to add simple IF statements without retrieving a "#VALUE" message?

    • Hello Nick,

      Sorry, everything works on my end. Make sure you format all source cells correctly and add one more closing bracket to the very first formula.

      Also, you may want to try the SUMIFS function instead.

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