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:

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:

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

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

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 3^{rd} 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":

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

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

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

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

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

**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")`

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

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

### 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))`

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

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

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:

**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)`

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

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

*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"))`

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

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

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

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!

## 607 comments

Hi,

Is it possible to give an IF to more than 1 rule at the same time?

I'm trying to create a formula to calculate someone's commission in Google.Doc's

The rule is if they reach their 100% target, the Sales £ they have earned will be multiplied by 1%. IF they exceed their target (E.G they achieved 125% of their target). the 100% achieved will still be multiplied by 1% but the extra 25% they earned will be multiplied by 1.25%. How do you make it so ONLY 100%> is at the higher rate of 1.25%

I've gotten this far so far: =IF(B7>1,(B6*1%)

Thank you

Hi, Jamie. You need a nested if. Look:

=IF(AND(B7>1, B71.25, B6*1.25%))

been trying to make a formula in google sheets

what I want to make is there should be a dropdown menu for Yes or No, but only if another cell in another sheet says No, because if that one says Yes there shouldn't be a dropdown menu and it should just say Yes. And the opposite is also true. Of course it would be cool if the dropdown menu still works and it's just linked to that other cell.

What does work is

=IF('Side Quests'!A50="Yes";"Yes";"No")

but then I can't use the dropdown menu anymore and that would mean I can only change the value on that one sheet and not on the one where this formula is. Is there something that I'm missing here? Is it possible to link both cells to eachother, in a way that they both have the same dropdown menu, and if you change one, the other will change to the same?

Thanks in advance ^^

I'm afraid you won't be able to do that with basic formulas. You need to use scripts to solve the task.

You may try to find the solution here - an overview of Google Apps Script with a lot of helpful content and links.

I have a google form that submits the email address of the user and enters it to field A2. I have another field, B2 that is calculated to capture that email address. The problem: The form goes through 3 levels for approval, and at each level the email is captured and replaces the information in field A2. My goal is to capture the applicant email address, which is the first email entered.

I am also using an arrayfunction that completes all rows. I can't figure out how to make it only get the first entry. Can you help?

Clarification: My goal is to capture the applicant email address, which is the first email entered in A2 by copying it to B2 and not have B2 change again when A2 captures the next email address in A2.

Hello, Brenda,

I'm sorry, but there's no way to prevent formulas in Google Sheets from recalculating. There are a couple of spreadsheet settings that can reduce the number of recalculations, but only for few formulas. You can check the list by going to

File > Spreadsheet settings > Calculations.I wish I could help you better.

thanks

I am trying to make a formula for my math teacher to do grades on Google Sheets. I am trying to accomplish the following: If column B has the text "A-" or "A" or A+", then column C should be filled with the number 4, if column B has the text "B-" or "B" or B+", then column C should be filled with the number 3, if column B has the text "C-" or "C" or C+", then column C should be filled with the number 2, if column B has the text "D-" or "D" or D+", then column C should be filled with the number 1, and if column B has the text "F-" or "F" or F+", then column C should be filled with the number 0.

Thank you for the detailed description of the task, Tarun.

If you'd like to do that with the IF function, put the following to C2 and copy the formula down (assuming the data in column B starts in B2):

=IF(LEFT(B2,1)="A",4,IF(LEFT(B2,1)="B",3,IF(LEFT(B2,1)="C",2,IF(LEFT(B2,1)="D",1,IF(LEFT(B2,1)="F",0,"")))))

Alternatively, you could create a "helper table", where column E would contain the list of all grades, and column F would have corresponding grades. Then, the following should also do:

=VLOOKUP(B1,$E$1:$F$15,2,FALSE)

Hope these formulas help!

Can you help me write a correct formula?

Calculate percentage of difference between two cells when both cells are non-zero.

Currently have the following but getting error - not sure how to check for non-zero cells and only proceed with calculation when they are.

=IF ((G419>0)AND(G445>0)),(((G445-G419)/G445)*100)

Thank you!

Cathy,

Please try the following:

=IF(AND(G419>0,G445>0),((G445-G419)/G445)*100,"")

If you'd like to know how to build the formula like this correctly, please look through this article. It's written for Excel, but the function works the same in Google Sheets.

Hope this helps!

I have a column in my sheet containing fares that I want to auto-populate based on a vehicle type AND a route:

Column A : vehicle

Column B: route

Column C: fare

Routes originating at an airport (but not a specified airport) cost more than others so the formula I am trying to use (in column C) but gives #ERROR! is:

=if(AND((A1="Sedan",B2="%Airport%"),"$350","$330", if(AND((A1="SUV",B2="%Airport%"),"$400","$350", if(AND((A1="minibus",B2="%Airport%"),"$$550","$500"))))))

If anyone can help me out with where the parse error is that would be awesome.

Thanks!

Thank you for contacting us, Kate.

First, if you want to check for any airport occurrences, you need to use the ISNUMBER+SEARCH combo instead of wildcard characters.

Also, you used one excess opening bracket for your AND functions. It should be like this:

AND(A2="Sedan",ISNUMBER(SEARCH("airport",B2)))

Once you fix that, the formula will return another error because you used more arguments than the function allows. You see, if your first condition is met, the formula will return $350, if not - $330. That's it, this is the formula.

In order to check other conditions, you need to replace all the second numbers with your next IFs:

=IF(AND(A2="Sedan",ISNUMBER(SEARCH("airport",B2))),"$350",IF(AND(A2="SUV",ISNUMBER(SEARCH("airport",B2))),"$400",IF(AND(A2="minibus",ISNUMBER(SEARCH("airport",B2))),"$$550","$500")))

If this is not what you need, please send me the example of your table (10-20 rows) to support@ablebits.com with the link to this comment.

I'll do my best to advise you.

Hello, Sean:

Try this:

=IF(ISBLANK(Inventory wk5!I2),Inventory wk5!I2,Inventory wk4!I2)

The ISBLANK function tests to see if the cell is empty, which is what I think you're testing. If you want to see if the cell is 0 then the formula should say =0. The zero character is a character and the cell would not be empty. Same thing for non-printing characters in cells. Those cells are not empty.

I am trying to create a formula to reference a cell from one sheet (if filled) or another sheet (if the first is not filled). This is what I have and it is not working.

=IF('Inventory wk 5'!I2>=0,'Inventory wk 5'!I2,'Inventory wk 4'!I2)

Anne:

Where column 1 is column A and the data is in A2 enter this in column 2:

=IF(A2<=40,A2,40)

I have a super simple scenario but don't know how to do this. I have 2 columns and I want column 2 to be: 'If the value in column 1 is less than 40, the column 1 value should appear as is. If the value is above 40, then the number 40 should appear. Any help would be appreciated! Thank you!

yeah, sorry it's work now..but i just wondering why it is didn't work before...well..can you help me to add an additional features like days of due date pass? the formula of spreadsheet its really hard for me than programming lol

Gian:

The formula you have here should work. What is wrong with it?

No that is error.

Logical error

=if(and(isblank(a1),b1<=c1),"Due Date","")

error. i want that if a1 is blank and b1<=c1 then "Due date" else "";

I need help, I am trying to get my excel sheet to populate an answer from a drop down selection. I need to make a simple "yes, no" drop down selection where when i pick one of the outcomes the cell next to it will come up with the different options to select from. For instance, if i select yes, the column next to it will automatically populate with some drop down list of options to choose from where if i select no then another separate list of option will pops up. Please help if you can. Thank you.

We have a special article that explains how to create a dependent drop-down list in Excel. You'll find it here.

I'm trying to create a formula in my sheet which would turn one cell grey if another cell contains a certain word. For example If C2 contains the word "clinic" I need E2 to turn grey. However, C2 contains the name of the class along with the word "clinic" in some cases, which has me stuck. Can I do this?

Sarah, sure you can. This tutorial should help.

I am trying to write a formula that will read a value and if the value is for example an A, B, or C then fill the next cell with a P1. But if it is D, E, or F, then P2. Can this be done?

Hello, Teri,

Sure. Assuming the value you check is in B2, you need to put the following into your "next cell":

=IF(OR(B2="A",B2="B",B2="C"),"P1",IF(OR(B2="D",B2="E",B2="F"),"P2",""))

Hope this helps.

Ohk I am trying to create a formula for the following. I am using a booking form that filters the number of beds and baths into an excel spreadsheet. But what I need for it to do is calculate the price depending on the number of x beds and x baths:

The parameters are:

A customer books a 'Regular Cleaning Service'

A 1 bed, 1 bath = $89.00

A 2 bed, 1 bath = $109.00

A 3 bed, 1 bath = $129.00

A 4 bed, 1 bath = $161.00

A 5 bed, 1 bath = $177.00

However if a customer was to add an additional bathroom it would cost another $32.00.

A customer books a 'Spring Cleaning'

A 1 bed, 1 bath = $127.00

A 2 bed, 1 bath = $147.00

A 3 bed, 1 bath = $177.00

A 4 bed, 1 bath = $237.00

A 5 bed, 1 bath = $267.00

However if a customer was to add an additional bathroom it would cost another $30.00.

A customer books a 'End of Lease Cleaning'

A 1 bed, 1 bath = $292.50

A 2 bed, 1 bath = $360.00

A 3 bed, 1 bath = $450.00

A 4 bed, 1 bath = $600.00

A 5 bed, 1 bath = $900.00

However if a customer was to add an additional bathroom it would cost another $90.00.

I am just a complete novice at this thing and would love some help :)

Leigh:

If these are the only three possibilities then

=IF(B11="Option 1",R21,IF(B11="Option 2",R28,R36)

Thank you Doug.

How would I add a 4th / 5th ... option?

Leigh:

You can add a few more IF to the formula like this:

=IF(B11="Option 1",R21,IF(B11="Option 2",R28,IF(B11="Option 3",R36,IF(B11="Option 4",R37,IF(B11="Option 5",R38,Value if not Option 1,2,3,4 or 5)))))

I am trying to copy a value from different cells on the same sheet, dependant on the option specified in cell b11.

for example:

if b11 = option 1 input value from cell r21

if b11 = option 2 input value from cell r28

if b11 = option 3 input value from cell r36

Well Done!. I have seen a lot of examples. Yours was spot on!

Want to scan 3 cells for text equal to "Scheduled". If yes, then add a number of minutes, (each cell takes a different number of minutes) if no add 0. Add up the total number of minutes required to schedule. I don't want to save values to another hidden cell and the add up if possible.

These are what I tried.

=(=IF(Sales!B5="Scheduled",20,0)+(=IF(Sales!D5="Scheduled",60,0)+(=IF(Sales!F5="Scheduled",90,0))))

Or

=SUMIF((=IF Sales!B5="Scheduled",20,0) (=IF Sales!D5="Scheduled",60,0)+(=IF Sales!F5="Scheduled",90,0))

=SUMIFs(Sales!B5="Scheduled",20,0, Sales!D5="Scheduled",60,0, Sales!F5="Scheduled",60,0)

Didnt work either

Hello, Zak,

It looks like you got the SUMIFS arguments wrong. Please take a look at our articles that show examples of the formulas with SUMIF(S):

SUMIF in Google Sheets with formula examples

SUMIFS in Google Sheets to sum cells with multiple criteria

Hello,

I am looking for a formula. I am working on two sheets, sheet 1 I am transferring info that needs to be tracked and I have set all the formulas for pulling over the data I need. However, I don't want the data transferred to sheet 2 unless one certain cell has data. Only needing a portion of what is on sheet 1. As is now, sheet 2 is pulling over all info. So there is a column in sheet 1 that if blank I don't want the data to transfer.

Thank you

Hi,

I need help with a formula...

I would like B62 to change red if J62<0. I could not find any examples or other's questions that resembled this...

I have tried several formulas like:

=if(J62<0) with and without quotes, commas, etc.

Please help?

Thanks in advance,

Di

PS...

I also changed the formatting style to red and bold...

Thx!

Di

Hi Di,

You should find the answer and examples in this article devoted to conditional formatting in Google Sheets.

Goodmorning. Please I need help to get a formula to find the grades of a total mark. Here are the grades below :

80%-100%:1, 75%-79%:2, 70%-74%:3 and so on

Please take a look at the article about the COUNTIF function. https://www.ablebits.com/office-addins-blog/countif-google-sheets/

Wondering how to do the following:

C3 = RED, YELLOW, GREEN

If RED, then C7 = RP

If YELLOW, then C7 = YP

If GREEN, then C7 = GP

Can I put more than one OR together??

TIA!

Diane

Nevermind!! I figured it out.

=if(C3="RED","RP",IF(C3="YELLOW","YP",IF(C3="GREEN","GP")))

I'd rather use the IFS function. It's designed just for such cases. Its syntax is easier. Look, instead you can put:

=IFS(C3="RED", "RP", C3="YELLOW","YP", C3="GREEN","GP")

Need a formula to search column C for a SKU number and place the price of the SKU in column H.

Something like if column C = 30066 then enter $15.00 in column H.

I would have multiple SKU's in column C and would need to do the formula for each SKU.

Jennifer:

With the data you provided the formula to give you the result you want is: =IF(C37=30066,15,"T")

Where the SKU is in C37 you can enter this in an empty cell and format the result cell as currency. The formula says if C37=30066 then display 15 otherwise display "T".

Depending on the number of SKUs this approach will probably need to be modified to a nested IF, VLOOKUP or INDEX/MATCH. If the list of SKUs gets over ten or so another approach might be required.

I am creating a google sheet to use to balance my checkbook. I currently have it as a basic ledger but I want to create a formula to balance it with my current balance in my account. I need a formula that does this: I want to take the value of column G and subtract the values in all column D fields that have the "N" in column F

(Column G is my current balance, "N" in column F means it has not yet cleared my account, column D is a transaction amount that has not cleared yet). I tried this formula but it only deals with one row, not every row that has a N in column F. =IF(F453="N",MINUS(D453,G453))

Thank you!

In cell B2, I am looking for the following Values:

A,A1,A2,B,B1,B2. If any of the values are TRUE I wish to populate K2 with the letter M and if none of the values are found (False), I wish to populate K2 with F (The populated values stand for Male and Female.)

I'm having problems with my IF formula. Please help.

Thanks

Here you can you OR as well, like I described above. In you case K2 formula will be: =IF(OR(B2="A", B2="A1", B2="A2", B2="B", B2="B2"), "M","F")

I am trying to do a nested IF statement for the following scenario:

In Row 1, Col B:E, I have letters A and C alternating in some of the cells but not all

In Col 1, Row 2:10, I have locations either Austin or Chicago (a location in every cell)

Inside the matrix (B2:E10) I want to put in a formula to mark an X in each cell IF it meets either of these conditions, otherwise leave it blank:

IF($B$2="A" AND A1="Austin", "X", IF($B$2="C" AND A1="Chicago, "X", "")

This formula is not working though, how do I make a new one that will?

Thanks

You can't use AND or OR like you did. They have to have arguments, for example: AND(A2 = "foo", A3 = "bar")

Thus, according to their syntax, your formula will be the following:

=IF(AND($B$2="A", A1="Austin"), "X", IF(AND($B$2="C", A1="Chicago"), "Y",""))

how would you activate it if there is any value in the cell given in the formula

Hi There,

Trying to use an IF formula to show that should a cell have a greater amount than another cell a different cell would show 'yes' and would show 'no' if it was not a greater amount.

For context this is for a stock check.

Thanks,

Hi Ray,

This part of the article explains how to create a formula based on a condition like the one you described.

Hello! I need help creating a formula for a spreadsheet. If a cell contains a certain range of numbers, how do I make the cell to the right of it, auto-fill in with a designated percentage.

For example:

If cell M4, contains any number between 0.00-79.99%, then cell N4 auto fills in with 3%

If cell M4, contains any number between 90.00-99.99%, then cell N4 auto fills in with 5%

And so on, based on the following chart.

% to Quota Bonus Rate

0.00%-79.99% 3.00%

90.00%-99.99% 5.00%

100.00%-109.99% 7.00%

110.00%(+) 9.00%

Appreciate the help!

Taylor, you need to add an extra IF as a logical expression to your formula, please have a look:

=IF(M4>0,IF(M4<79.99%, 3%,IF(M4<99.99%, 5%, IF(M4<109.99%,7%,9%))),"")

In this case Google Sheets checks if M4 is more than 0; then it checks whether M4 is less than 79.99 and puts 3% if it is, or keeps checking further. Please take a look at the part "IF in combination with other functions" in this article.

Is it possible to copy selective data from one tab of a sheet to another tab. I have data in column A through Y. And, I want to copy only the name (column A and url column (column V) only if the url exist for that name, not if the url column is empty.

Any help would be appreciated.

You need the VLOOKUP function. You can also try the add-on Merge Sheets, it solves your case perfectly without writing any formulas! You'll find the links below:

https://chrome.google.com/webstore/detail/merge-sheets/gdmgbiccnalapanbededmeiadjfbfhkl?hl

https://www.ablebits.com/office-addins-blog/vlookup-google-sheets-example/

Is it possible to create an IF formula to keep a running count of the number 1 in a column of cells, ex. E36 to E64

I am using this IF formula to multiply if the number is greater or = too but it doesn't work past the 1st IF. All are multiplied by 1.15 no matter the number value of the number. Please help!

=if(isblank(K2),"",if(K2>=45,K2*1.15,if(K2>=40,K2*1.2,if(K2>=35,K2*1.25,if(K2>=30,K2*1.5,if(K2>=25,K2*1.75,if(K2>=20,K2*2,if(K2>=15,K2*3,if(K2>=10,K2*4,if(K2>=1,K2*6,0))))))))))

Your formula works great. I've just checked and if K2=40, the result is 48; if it equals 1, then the result will show 6.

Hi,

I have two columns which you can choose if the payment made was "CASH" or "CHECK".

I was successful in setting up the "CASH" part wherein if I choose the first column as "CASH" then the other cell will automatically set the value/text as "N/A".

My problem is when I now choose "CHECK", I am hoping that the other column will be a free cell, wherein I can write anything or specifically numbers. (for check numbers that were used to pay) without erasing or deleting the formula/function that was set or written.

Appreciate the suggestions. Thank you.

I am trying to create a payment list that includes a drop down menu on every row (PAID & NOT PAID). Another column is how much each person owes, I7:I105 is the payment status, F7:F105 is how much is owed, F107 is the total money owed altogether. So far ive created the drop down menu on each row, totalled all the money into F107 and when the payment status is set to 'PAID' the whole row will change to red with strike through text.

What i want is when the payment status is set to 'PAID' for the whole row to turn red with strikethrough text and the money a person has paid to be deducted from the total money owed in F107. HELP!!!!

You need another function COUNTIF. You'll find it here: https://www.ablebits.com/office-addins-blog/countif-google-sheets/

I am doing a stock sheet, i want to say if a number is entered in the delivery slot, add this number to the remaining stock cell and change the total stock cell accordingly.

J11 Stock Delivered

G11 Stock Remaining

D11 Total Stock

I need a formula that copies data from several cells on one sheet to identical cells on another sheet, IF a value is entered into a cell on one of the sheets.

if the value in F4 is "x" Then A4, B4, C4, D4, E4 and T4 need to be copied to (new sheet B3,3,D3,F3,G3,H3)

any help I'd appreciate.

Hello,

If I understand your task correctly, please enter the following formulas into the corresponding cells on the new sheet:

Cell B3

=IF($F$4="x",Sheet2!A4,"")

Cell C3

=IF($F$4="x",Sheet2!B4,"")

Cell D3

=IF($F$4="x",Sheet2!C4,"")

Cell F3

=IF($F$4="x",Sheet2!D4,"")

Cell G3

=IF($F$4="x",Sheet2!E4,"")

Cell H3

=IF($F$4="x",Sheet2!T4,"")

Hope it will help you.

Hi, I am trying to populate a cell with a certain value (price) if the previous according to different values in another cell, example: if the day is sunday or monday the price would be 20euro, if is monday would be 15 euro... how shall I type the function? Thank you

Hello,

If I understand your task correctly, please try the following formula:

=IFERROR(IFS(WEEKDAY(A1,1)=1,"20euro",WEEKDAY(A1,1)=2,"15euro"),"")

where cell A1 contains a date value, e. g. 1/30/2018

Hope it will help you.

I am trying to make the IF statement work selecting one of two columns that has data. In every row either Row V or Row W will have data but never both and just want the formula to select which one has data. I have tried the ISBLANK statment but it sees the hidden formulas as data and won't return a value.

=IF(V2>0,V2,W2) This works is V has a value but if V is blank it won't return W

=if(ISBLANK(W7),V7,W7) This attempt will work to show the value for W but if V has a value it can't see pas the formula in W.

Any help would be greatly appreciated.

I've tried these two without success.

Tim

I like to have a function like

if a cell (ex B2) is empty-(blank) then ---- else 100)

i don t know how to tell him is empty

How do I insert one of several different formulas into a given cell, based on a one-time test?

For example, if C10:C15 hold 'frozen' (non-recalculating) random numbers, I want B10,say, to hold =E2*3 if C10 is less than 0.5, otherwise B10 should hold =0.

How do I go about this?

Hello,

If I understand your task correctly, please try the following formula:

=IF(C10<0.5,E2*3,0)

Hope this will help you!

I am trying to write some if and statements .. So I have a number crossword puzzle, if the students get the write combination I want it to say congrats. I am setting for smaller statements - two cells at a time, if I have to .. But I have something wrong.

=if(AND($A$3="5",$A$5="1"),"so far so good", " ")

Any help would be appreciated.

Hello, Rachel,

Please try the following formula:

=IF(AND($A$3=5,$A$5=1),"so far so good", " ")

Hope it will help you.

How would I create a custom formula in Google Sheets which would do the following:

If Column A, B, & C contains a date then change column X to YES

Cheers legend!!

In Google Sheets, how can I use an "if" conditional to change the color of a row based on one cell in that row? I am using conditional formatting for one cell (G3:G30), but I want the whole row to be the same color of that the color in the G cell in that row. Is there a certain code to indicate color? I am using 4 colors.

Incomplete=red

In Progress=orange

Ongoing=Yellow

Complete=Green

I know how to copy formatting for font/size, but not for cell color.

Help me to make a formula. I have given a date e.g: 1st August 2017. The due date is 7th August 2017. If the completion date is before due, it becomes EARLY. If it completed on the date same as due, it becomes ON-TIME. If it passed the due, it becomes LATE. Thanks in advance.

I am trying to pull in the value of C based on the value of W=A and X=B.

A1= "John"

B2 = "3/17/17"

C3= "20"

W4 = "John"

X4 = "3/17/17

Y4 = "20"

Hi there

Can you please let me know what the proper equation would be for assigning a specific value to a cell? For example, if a cell is populated (with text, doesn't matter what the text is) and I want to assign the value of 1.25 in the cell directly to the right of the populated cell, how would I type that out in an equation? IF C2 = filled, then 1.25 (or something similar).

Thank you!

If I get it right you'd put the formula in D2, where you will populate 1.25, if C2 has a whatever value. This if it can be whatever value, also a number, ecc... you can use the following formula:

=IF(C2"",1.25,"Field was empty")

you can replace "Field was empty with whatever you'd wish to do in case C2 was not populated.

Hi I'm trying to get the if statement to do a subtraction but it's coming up with parse error. What I'd like is a time i.e. 07:00 and the if statement subtracts from that by 0:15 if over 4:30, 0:30 if over 6:00 or 0:45 if over 8:00 how would I best wright this ?

I have 3 columns with three different values, I want a formula which can say Good or bad based on some If conditions.

Basically what I want is

A) The difference in values between Col C and Col A > 12

AND

B) Value in col C is > 160

If the above conditions match I want the fourth column to say "Bad" If it fails to match I want it to say "Good"

This is the formula I have used. =IF(AND(C1-A1 > "12",C1 > "160"),"bad","good") But irrespective I get Good all the time.

Can you help what is that I am doing wrong here in the formula?

Hi, Chandra,

Your formula is written incorrectly, it should be like:

=IF(AND((C1-A1)>12,C1>160),"bad","good")

You don't need to enclose numbers in double quotes and you need brackets for C1-A1. Please read this article about common mistakes made when writing the formulas.

Hope this solves your task.