Productivity How To Use the IF Function in Google Sheets

How To Use the IF Function in Google Sheets

Photo of author
By
/

When you buy through my links, I may earn a commission which will support me in creating more helpful content for you.

Do you know you can use the IF function in Google Sheets without much difficulty? It might sound like a huge task, but in reality, once you get a hang of it, it becomes pretty easy.

One of the most important functions in Google Sheets is the IF function, as it can help you easily scan through a spreadsheet of data and interpret some key information.

This tutorial article will focus on teaching you how to use the IF function to test different expressions and return a specific value for the first condition that’s proven to be TRUE.

Check out this course to get started with Google Sheets today! – Link Here

What Are IF Functions In Google Sheets

The IF function is a function in Google Sheets that evaluates a logical expression and returns the value on the basis of TRUE or FALSE.

The values shown can either be texts or numbers, or just the result of a series of other nested IF functions (functions like AND and OR). 

For example, if you have three separate columns in your spreadsheet; channel, target, and profit. You can use the IF function to determine the channels that hit a higher profit than the target. 

How to Use the IF Function

You can use the IF function for one logical expression. Also, it’s possible to put together more than one IF statement into a single formula for tougher expressions.

The formula for the IF function is:

IF = (logical_expression, value_if_true, value_if_false), where
  • Logical expression – It’s the reference to the cell containing an expression of either TRUE or FALSE
  • Value_if_true – It’s the value of the function when the expression is calculated as TRUE
  • Value_if_false – It’s the value the function returns when the expression is FALSE. 

You’ll also need the logical expressions;

  • = equals to
  • > greater than
  • < less than
  • >= greater than or equal to 
  • <= less than or equal to 
  • <> not equal

Using the IF Function In A Single Logical Expression

For this illustration, we’ll use the market sales percentage of a group of business owners, to know which of them recorded a profit and those that recorded a loss in the space of 1 year. 

Learn how to make Heat Maps in Google Sheets in this article.

The formula we’ll use is:

IF = (B2>50,”Profit”,”Loss”)

Step 1: Input all values into the spreadsheet

Step 2: Choose an empty column for your calculation. Preferably the next column, so it looks more arranged.

Step 3: Type IF into the selected column

Step 4: Input the cell you want to reference 

Step 5: Input the logical expression you are trying to achieve (i.e >50)

Step 6: Type in comma (,) and write the text you want to return for value if TRUE, and value if FALSE, respectively. Don’t forget the quotation marks after each text, or you’ll get it wrong.

Step 7: Click ENTER

Alternatively, you can click on Auto-fill to help you fill out the texts for each row.

Using The IF Function In Equals To (=)

For this illustration, we’ll still use the same data as the previous example. So, the plan is to figure out sales values that are equal to 88 and label it as PROFIT.

The formula is:

IF(B2=88,”Profit”,” “)

Note: The steps are pretty much the same, you only have to put the distinct IF function peculiar to the logical expression you are trying to figure out.

Step 1: After putting in your data, input the IF formula

The reason why the value_if_false space was left blank is for it to reflect as a blank space in the Google Sheets. Whatever text you put in will return if the value is FALSE.

Step 2: Press ENTER

Using the IF Function in Greater Than ( > )

Similar to the other two illustrations that we have done; use the IF function to determine sales that are above a particular percentage.

In our spreadsheet, we’ll use the IF function to figure out sales percentages over 60 and label them as profit, while the figures less than 60 will be left as blank. 

Step 1: Input data in your spreadsheet (follow the procedures of the first illustration)

Step 2: Input the IF formula in the next blank column. The formula for this example is 

=IF(B2>60,”Profit”,” “)

Step 3: Click Enter

Using The IF Function in Less Than (<)

It’s also possible to use the IF function with the less-than operator as well. The IF function can help you determine the business owners that passed the yearly quota percentage and those that didn’t.

The quota percentage is 70%, and those above will return as TRUE (indicated as YES), while those that didn’t will return as FALSE (indicated as NO).

For this illustration, we’ll use the formula;

=IF(B2<70,”YES”,”NO”)

Step 1: Input your data

Step 2: Type in the IF formula

Step 3: Click ENTER

Using the IF Function to Check Multiple Logical Expressions

This particular formula is mostly used when you need to perform grading in clusters. It’s mostly used in educational settings to determine the grades to give to students that fall into a particular range.

You can also use this formula

However, our illustration will be that of individuals in a marketing sector and the number of potential customers they can convert into actual clients. We’ll base their performance on the number of customers they can convince to patronize their brand.

For example, a worker gets a “pass” if they convert 10 – 30 clients, “good” if they convert 31 – 70 clients, “excellent” if they convert 71 – 100 clients, “perfection” if they convert 101 – 250 clients, “distinction” if they convert 250 – 500 clients.

The formula for this expression will be:

=IF(B2<30,"PASS",If(B2<70,"GOOD",If(B2<100,"EXCELLENT",If(B2<250,"PERFECTION","DISTINCTION"))))

Note: Count the number of “IF” in the equation, that’s the number of ending parentheses you are to use. 

Step 1: Input your data

Step 2: Type in the IF formula

Step 3: Click Enter

Using the IF Function in AND/OR 

With this function, you can check multiple logical expressions at the same time. 

For instance, if the criteria for offering awards to workers in a marketing firm is converting above 70 clients, and getting more than 60% revenue, then you can use the AND/OR operators to figure this out easily.

For this expression, we’ll use;

=IF(AND(B3>70,C3>60%),"YES","NO")

Step 1: Input the data

Step 2: Type in the IF formula

Step 3: Click ENTER

You can see that only the workers who had above 70 clients, and above 60% in revenue were awarded. 

Difference Between IF-Then, IF-And, IF-Or

Sounds confusing right? But we’ll break it down in very simple terms. 

IF-THEN: This is when you are checking if a single condition is met. The return value is either TRUE or FALSE.

Example: I will not be at home today. If you are not at home today, then the return value is TRUE, however, if you are, the return value is FALSE.

IF-AND: This is when you are checking for a series of expressions, and all conditions have to be met for the return value to be TRUE. 

Example: I’m going to the salon and I’ll weave my hair and paint my nails. For this value to be TRUE, you need to perform all three actions. If you end up going to the salon and weaving your hair without painting your nails, it’ll turn out FALSE.

IF-OR: Unlike the “IF-AND” function, this function checks multiple expressions, but if one turns out to be correct, it’ll return as TRUE.

Example: I am going to the saloon to paint my nails. If you end up just going to the saloon without painting your nails, it’ll still return TRUE. it’ll only return as FALSE when you do neither. 

IF Function Frequently Asked Questions

Is There Any Difference Between If And Ifs In Google Sheets?

Yes, the IF function is for a single logical expression, while the IFS can be used to check multiple logical expressions. Also, when using the IF function, you can customize the FALSE value to return, but you won’t find this option in the IFS function.

How Does the IFS Function Work?

The IFS formula checks whether all the criteria are met, or if even one is met, and gives a value that’s in line with the first TRUE expression. 

Is It Possible To Use Two IF Statements in Google Sheets?

While this is very possible, it’s also a confusing path, and most times you might make certain errors. If you want to use more than one IF statement, then it’s best to use the IFS formula.

What Is IF Nesting?

Nesting in Google Sheets means putting the IF function within its formula TO test for multiple expressions and return different values based on the tests. 

Conclusion

The IF function is highly useful in Google Sheets, and it’s a very flexible tool that you can use in evaluating different conditions. It assists in making data aggregation and other spreadsheet tasks less daunting and complex; plus it’s very efficient.

Using the IF statement, you can unlock new dimensions and increase your depth in Google Sheets.