May
16

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

What is IF function in Google Sheets?

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:

Decision tree of the alternative question.

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.

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

Usage of the IF function

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:

Sample sales data for 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).

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

Functions prompts in Google Sheets.

After that, all our actions will be accompanied with 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 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".

Our function should look like this:

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

And F2 returns the text "Our Country".

Google Sheets IF function.

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.

IF function and text values

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

Note. Please pay attention that if the text is being used as the argument, then it must be enclosed in double quotes.

IF function and numerical values

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 not only to fill the cells with certain numbers based on the conditions met, but also to make calculations.

For example, let's say we give our clients the 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)

IF with numbers in Google Sheets.

IF in combination with other functions

As we have already learnt, 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")

IF with OR in Google Sheets.

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

Google Sheets nested IF.

Note that we used IF function as a second argument of the IF function. In this case, the decision tree is as follows:

Nested IF decision tree.

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

Using AND in Google Sheets nested IF.

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.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard