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, we will take a closer look at how Google Spreadsheet IF function works and what advantages we can get from using it.

Whenever we use IF function, we 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 how a decision tree may look like:

So, IF function allows us 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.

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 as "=", ">", "<", ">=", "<=", "<>". Now we'll try and ask such a question together.

Let's assume that you are working in the company which sells chocolate in several consumer regions and works with many clients.

This is how your sales data look like in Google Sheets:

We need to separate sales which were made in our local regions from those which were made abroad. To accomplish that, we should add another descriptive field for each sale - a country where the sales took place. Since we have lots of data, we want this description field to be created automatically for each entry.

And this is when 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).

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

After that, all our 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 - 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 a text "Our Country".

And again, after the comma write in 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".

Our function should look like this:

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

And F2 returns the text "Our Country".

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

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

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

We can use numbers for the arguments just as we did it 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 we give our clients discounts based on the total value of the purchase. If the total is more than 200, then the client receives a 10% discount.

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

`=IF(E2>200,E2*0.1,0)`

As we 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.

Remember the way we figured out the country where we sold chocolate?

Now we can use OR function as a first argument, which will return "YES" if at least one of the listed conditions is true. We simply change the IF function in the cell F2. For the first argument, we will list all the regions which 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")`

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

Let's assume that we have set stricter discount conditions for our clients. If the total purchase is more than 200 units then they get a 10% discount; if the total purchase is between 100 and 199 then 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 we used IF function as a second argument of the IF function. In this case, the decision tree is as follows:

But what if we make it even more fun and complicate the task? Imagine that we're offering the discounted price to the one region only - "East".

To do that correctly, we add the logical expression AND to our function. The formula then will 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 have reduced greatly while their amount remains intact.

We 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 to data processing in Google Sheets.

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 230 Responses to "Google Sheets IF function - usage and formula examples"

MILES RATE TOTAL

34 1.88 $366.22

How do i put a mileage range of numbers like this

miles rate

0 to 5.9 = 1.01

6 to 10.9 = 1.11

11 to 15.9 = 1.25

and so on in to a formula any help would be great.

Hello Patrick,

If I understand your task correctly, here's how you should put that down to the formula (supposing everything starts from A1):

=IF(AND(A2>=0,A2<=5.9),1.01,IF(AND(A2>=6,A2<=10.9),1.11,IF(AND(A2>=11,A2<=15.9),1.25,IF(AND(A2...),1.88,IF...))))

I'm trying to populate a certain text in column F if column c says a specific thing. I'm using =IF(C3:C237 = "DMM901Y", "No PPW Status Available", " ") on the F column, but it's not working. All help is appreciated. Thank you!

Edit:: It works on a singular cell, but not the whole column. I want the rule to work for the whole column.

Thank you for your question, Lindsay.

Try wrapping your IF with an ARRAUFORMULA:

=ARRAYFORMULA(IF(C3:C237="DMM901Y","No PPW Status Available",""))

If you're wondering how it works, please check this blog post.

=IF(B2>=96.01%,"5",if(B2<96%,"4",if(B2<95%,3,IF(B2<94%,2,IF(B2<92%,1)))))

Help on this please.

1 96.01%

Hello Mark,

You need to introduce AND to your conditions. Please see my reply to Patrick above, he had a similar logic in his task.

If I want to use this function on existing data, and if the predicate is false I do not want the row to change, how do I go about that?

If I'm getting it correctly, just live the argument for the "false" result empty.

How do we use the if statement to redirect to a website?

if value=true

then the person is redirected to a website.

Hello Aditya,

Using the formula, you can only make it return a webpage for the user. Just enter the hyperlink as an argument, and it will appear if value=true. However, the person will have to click the link in order to open it.

I would like that if A1 is 0 then put cell B1 in cell C1.

Is this possible?

Looking forward to your help!

Hello Michael,

yes, it is possible. Enter the following formula to your C1:

=IF(A1=0,B1,"")

I am trying to add an IF statement that would add a multiplier if they play for a certain team. So like, I have rows of stats, name and team. So IF they play for the rockies they would have a .9375 multiplier. However the thing I need it multiplied to is already a formula. So can I add an if statement to a formula, if so, how?

Hello Jackson,

For me to be able to help you out, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.

Note.That email for file sharing only. Please do not email there. Once you share the file, just confirm by replying here.I am looking for help to create a formula that will calculate tax liability as follows:

1.) If annual income is less than $42,900 then tax is calculated by multiplying the tax amount by 5.8%

2.) If annual income is $42,900 or greater and is less than $101,550 then tax is calculated by multiplying the difference between $42,900 and the annual income by 6.5% then adding $2,488.

3.) If annual income is greater than $101,500 then tax is calculated by multiplying the difference between $101,550 and the annual income by 7.15% and adding $6,447

Any assistance would be helpful!

Hello Christie,

Please check if the formula below works as desired. It is written supposing that your tax amount is in column A and annual income is in column B, so just make the necessary adjustments based on your data:

=IF(B2<42900,A2*5.8%,IF(AND(B2>=42900,B2<101550),(B2-42900)*6.5%+2488,IF(B2>101500,(B2-101550)*7.15%+6447)))

Hello,

I'm trying to create a fixed daily summary of the data below.

Date Fruit Name Quantity

4-Nov-19 Apple 10

4-Nov-19 Orange 20

4-Nov-19 Grapes 50

4-Nov-19 Banana 15

5-Nov-19 Apple 20

5-Nov-19 Orange 50

6-Nov-19 Orange 20

6-Nov-19 Grapes 50

6-Nov-19 Banana 15

This below is the summary format I want to do

Summary

Date Apple Orange Grapes Banana

4-Nov-19

5-Nov-19

6-Nov-19

...

Hello,

I'm afraid IF is not the function you'd normally use to find matches.

You can either try the add-on described here to lookup matches by all conditions (date and fruit), or create a pivot table out of your source data.

I'm doing of spread sheet of our household bills and want to show the difference from the previous month with some result being + change some being - change. How do i do that?

Hello Jerel,

We described different ways of comparing Google sheets in this blog post:

https://www.ablebits.com/office-addins-blog/2019/04/30/google-sheets-compare-two-sheets-columns/

For google sheet

If(or(A1="",A1="TBD"),"",text(A1,"yyyy")))

Why this formula returns TBD if A1 contains TBD?

Hello Nadeem,

I'm sorry but the formula you provided can't return TBD in case it's already in A1. A cell with this formula will remain empty in this case.

I kindly ask you to double-check the formula in your spreadsheet and the contents of A1.

I have a column that will a yes or no. Based on the answer, I would like for another column to calculate a tax. Example, if cell F13 is Yes, then I would like to look at an amount in cell G13 and calculate the tax based on the amount in cell G13. Then if cell F13 is No, I would like for it to look in cell G13 and calculate the tax based on the amount in cell G13. Bottom line, if a person is married (Yes) then there will be one calculation and if a person is not married (No) there will be a different calculation.

Hello Robert,

If I'm getting your task clear, a formula like this will do:

=IF(F13="Yes",calculation_for_YES_with_G13,calculation_for_NO_with_G13)

In case you're still not sure how it works, please describe how your data is arranged in more detail.

Hello, I am trying to create an inventory tracker to track multiple items being sold at multiple locations. I have an Inventory sheet that has Product A listed (lets call that A2) and then a numerical quantity under Original Stock at Location A (B2). Then there's Current Stock at Location A (C2). Then I have a Sales sheet. What I'd like is when a sale is recorded for Product A at Location A, for C2 on the Inventory sheet to equal (B2- the new sale). I was able to make an IF formula that works when the sale is recorded in a specific cell, but the sales will be listed in chronological order so I'm trying to make the formula work for the entire column all the way down. Is there any way to do that? Thanks so much!