IF Then Formula in Google Sheets—Detailed Guide

Do you ever need to check a condition in Google Sheets and return This or That values? Like test whether “5 < 6” and get “Yes” or “No.”

Well, that’s what the Sheet’s IF function is built for. It is one of the most used logical functions in Sheets.

Above, I just gave the simplest example. The IF function has more advanced usage that could prove to be helpful on a day-to-day basis.

In this article, I have covered everything about the IF function with examples—including IF with multiple criteria, IF for Conditional Formatting, and many more.

Arguments Used in IF Function

Before you use the IF function, it’s important to understand the function arguments. Here’s a very simplified version.

How to Use IF Function in Google Sheets?

Firstly, let us look into the very basic example of the IF Function. 

Return Text String

Using the IF function, you can return the Yes/No, True/False, Qualified/Not Qualified, Good/Bad or any two values for the criteria. 

Suppose, 8 people are participating in a Swimming competition. The Age limit for the participant is less than or equal to 35. 

I want to check whether the participants are eligible for the competition or not based on their age. Then, Return Yes/No statements.

To test, my formula would be

=IF(C5<=$F$5, "Yes", "No")

In the IF formula above, C5<=$F$5 is our condition. It checks whether the value in C5 is less than or equal to F5 or not. Since the criteria is TRUE, we got Yes.

To apply the formula for the remaining cells, extend the Smart Fill handle. 

Return Numbers 

It isn’t necessary that the value to return in the IF function has to be a text string. You could also use it to return the numerical values. Here’s another example.

Let’s assume, Julia has a list of Products and Prices. In her store, she has a scheme that the customers who purchase 10,000 or more will get a 5% discount.

Based on the Price, we will check whether the customer will receive a discount or not. 

In the Discount column, I entered this formula. 

=IF(C4 >=$G$4, $H$4, 0)

In the formula, C4 >=$G$4 is our criteria. This time, we have used the cell reference ($H$4) for our value_if_true argument and 0 when false. 

Here, we got 5%. Similarly, after we got the results, we copied down the formula. 

Perform Conditional Sum

Moving on, the IF function is also best to perform Conditional Sum. 

In a Spreadsheet, I have a record of Groups, their Projects, and Deadlines. If the Group is “Involved” in Another Project, I want to extend their deadline by 3 days.

In short, add the Deadline Dates by 3. Similarly, in case they are “Not Involved,” I want to return the same deadline.

In the New Deadline Column, I entered this IF formula and copied it down.

=IF(E4 ="Involved", C4+D4, C4)

Since the value in E4 was Not Involved, we got the same deadline. 

But, in F5, we got a new deadline as the condition is True. 

Perform Conditional Multiplication

Just like the Conditional Sum, you can also compute the product of numbers based on criteria.

In Column B and Column C, we have the Salesperson’s name and Total Sales respectively. Based on their Sales value, we will calculate the bonus. Here are the criteria.

• 5% Bonus: If Total Sales > 10,000
• 2.5% Bonus: If Total Sales < 10,000

Now, let’s find out the bonus for each salesperson using the formula which is 

=IF(C4> 10000, C4  * 5%, C4  * 2.5%)

If the criterion is true, the formula returns a 5% bonus and 2.5% for false. 

IF Function With Multiple Criteria

While the IF function is pretty useful, it comes with a limitation. The IF function can test only one criterion at a time. 

But, wait! We do have a workaround to use the IF with Multiple Criteria. You can nest IF with IF to achieve this. 

Formula: 

=IF(logical_expression, value_if_true, IF(Logical_expression, value_if_true, [IF(Logical_expression, value_if_true,....)))

Example: 

Let me take the same example as Example 1. Above we would either get “Yes” or “No.” But, this time, I’ll add an extra value to return which is “Maybe.” So, as a total, we will test three conditions in the IF function. 

• Return Yes if Age < 35
• Return No if Age > 35
• Return Maybe if Age = 35

In the formula cell, I entered the formula as

=IF(C5 <$G$5, "Yes", IF(C5>$G$5, "No", IF(C5 =$G$5, "Maybe")))

In each IF function above, we have entered the logical expression and value_if true. So, we got the results accordingly.

Nest IF with Other Functions

Apart from using the IF function solely to test criteria, you can also nest it with other functions such as ISBLANK, OR, AND, etc. 

IF and AND function

In Sheets, you could also construct a formula using the IF and AND functions together. Just like the IF with IF, it is also best used to test multiple conditions. 

I recommend you opt for this formula when you have different items in the next column for the duplicate value. 

Formula: 

=IF(AND(logical_expression1, [logical_expression2, ….],value_if_true, value_if_false) 

Example: 

Let’s assume, I have only a Black Backpack in stock at the moment. Now, from the list of bags, I’ll return Yes if it is Backpack Bag in black color and No for the rest.

To return Yes/No, my formula would be

=IF(AND(B6 ="Backpack", C6 ="Black"), "Yes", "No")

The AND function returns TRUE when B6 ="Backpack" and cell C6 ="Black.” Then, for TRUE, the IF will return “Yes.” Similarly, “No” for FALSE.

IF and OR function

The next function that goes well with the IF is the “OR” function. Try IF and OR, when you want to reference at least one of the logic in a cell. 

Formula: 

=IF(OR(logical_expression1, [logical_expression2, ….],value_if_true, value_if_false)

Example:

Suppose, I have Bags in the Product Column. Only Tote Bag and Cross Body Bags are delivered. So, let us return “Delivered” for those bags and “Not Delivered” for the remaining ones.

To test, I constructed the IF and OR formula as mentioned in the box.

=IF(OR(B6 ="Cross Body Bags", B6 ="Tote Bag"), "Delivered", "Not Delivered")

IF with ISBLANK 

When you’re doing tasks on a sheet, blank cells in data can make a big impact on your results. But, if you just know how to dodge blank cells or use them in a different way, it won’t be a serious deal.

For Instance, you can look for blank cells beforehand and fill them with a value. At the same time, return the results in the rest of the columns. 

For that, I will be using the IF and ISBLANK together. ISBLANK tests if the value you’ve passed on is an empty cell or not. 

Formula: 

=IF(ISBLANK(value, value_if_true, value_if_false))

Example: 

Suppose, I need to return 2% discount for all the Prices in Column D. But, if the cell is blank, let’s return the “No Value.”

To check, here’s the nested IF and ISBLANK formula I used.

=IF(ISBLANK(D5) = TRUE, "No Value", 2%)

As specified in the formula, I got “No Value” for the empty cells and 0.02 for the non-empty cells. 

Use IF Formula in Conditional Formatting

The sheet’s conditional formatting allows you to create your custom formula and highlight the cells based on criteria.

So, you can also use the IF function to set criteria in the Conditional Formatting. 

Example: 

If the value of Column C <= 35, I want to highlight the Names Column with a Yellow color. Here, the conditional formatting highlights only values where the IF function returns TRUE. 

1. Select Data Ranges.
2. Head to the Format Tab and pick Conditional formatting.
   
3. Below format rules, choose Custom formula is. Then, in the empty box, enter the IF formula as =IF(C5 <=35, TRUE, FALSE).
   
4. From the Formatting style, pick a Colour. Finally, hit Done. 

Avoid Cell Errors When Using the IF Function