IF Statement in Google Sheets: Formula & Examples

If you want to use IF statements in Google Sheets, you need to know about the IF and IFS functions. In this guide, you will learn how to work with IF statements in Google Sheets. After reviewing the IF function syntax and some useful operators for your IF statements, you'll learn how to use the IF function to test values using a logical expression and get a different value depending on whether or not it passes the test.

You also have multiple examples with step-by-step instructions: how to combine IF and AND to check that multiple conditions are simultaneously TRUE, how to nest IF functions to test different conditions in order and return the corresponding value for the first condition found to be TRUE. You also have an example of using nested IF functions and returning the result of different calculations.

Finally, you will learn about the differences between IF and IFS.

What are IF Statements in Google Sheets?

The IF function evaluates a logical expression or statement and returns one value if it’s TRUE and another if it’s FALSE. The values returned can be specific numbers or text, calculations, or the results of other functions. You can also nest IF statements to evaluate multiple conditions.

IF Function Syntax in Google Sheets

The syntax for the IF function in Google Sheets is as follows:

=IF(logical_expression, value_if_true, value_if_false*)

• logical_expression: a logical expression or test that evaluates to TRUE or FALSE. It can also be a reference to a cell containing the expression.
• value_if_true: the value that should be returned if logical_expression is TRUE.
• value_if_false*: optional. Blank by default. The value that should be returned if logical_expression is FALSE.

Useful Operators for IF Statements

Below is a list of useful comparative operators you can use in your logical expressions. You also have a list of mathematical operators you can use in both conditions and return values.

Comparative Operators

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

Mathematical Operators

• + addition
• - subtraction
• * multiplication
• / division
• ^ exponent

How Do I Write an IF Statement in Google Sheets?

To write an IF statement in Google Sheets, use the built-in IF function: =IF(logical_expression,value_if_true,value_if_false). For example, to return “Yes” or “No” depending on whether an amount is greater than or equal to $4000, you can use the following formula: =IF(B2>=4000,"Yes","No").

1. Add Condition

Open Google Sheets to the spreadsheet with your data. In the ‘Bonus’ column, type the IF function in the row for the first amount. The first argument is the condition or logical expression, which in this case, is that the cell in the amount column should be greater than $4000.

2. Add Value if TRUE

Add a comma followed by the value you want the function to return if the condition is TRUE. In this case, I have to enclose the word between quotation marks because it is text: “Yes”.

3. Add Value if FALSE

Add another comma, followed by the value that should be returned if the condition is FALSE. In this case, “No”, followed by the closing parenthesis.

Press ‘Enter’ to see the result. Grab the fill handle and drag it down to the last row.

How to Write an IF Statement with Multiple Conditions in Google Sheets?

There are various ways of using multiple conditions in an IF statement. In this example, you will learn how to use IF together with AND to check that multiple conditions are simultaneously TRUE.

1. 1. Type the IF function in the ‘Action’ column, followed by the AND function.

1. 2. Type the first condition as the first argument of the AND function. In this case, the value in column B must be equal to “Yes”.

1. 3. Add a comma and the second condition: the value in column C must be equal to “Yes”.

1. 4. Close the parenthesis for the AND function and add a comma.

1. 5. Type the value you want the function to return if both conditions are met: “Send email”.

1. 6. Add a comma and type the return value if one or both conditions are not met: “None”.

1. 7. Close the parenthesis and press ‘Enter’ to see the result. Grab the fill handle and drag it down to the last row.

How to Write a Nested IF function?

You can nest IF functions to check a series of conditions with their corresponding “TRUE” values, as well as the value to return if none are TRUE. In this example, I will check the sales amount to determine the corresponding bonus percentage.

1. 1. In the “Bonus %” column, type in the IF function and the first condition.

1. 2. For the second argument, type the value you want the function to return if the first condition is true.

1. 3. For the third argument, type the next IF function and use the second condition as the first argument.

1. 4. For the second argument of the second IF function, type the corresponding value to be returned if the second condition is true.

1. 5. For the third argument, type the last IF function and the third condition as its first argument.

1. 6. For the second argument of the third IF function, type the last condition and corresponding value if true.

1. 7. Finally, type the value to be returned if none of the three conditions are true.

1. 8. Close all three parentheses and press ‘Enter’ to see the result. Grab the fill handle and drag it down to the last row.

More Examples of IF Statements in Google Sheets

For this example, I will use the same data as in the previous section, but instead of returning the corresponding bonus percentage, I will calculate the bonus amount directly by multiplying the amount by the bonus percentage.

1. 1. In the “Bonus Amount” column, type in the IF function. Type the first condition and the corresponding calculation, each followed by a comma.

1. 2. For the third argument, type the next IF function and use the second condition as the first argument. Add a comma and the calculation to perform if TRUE.

1. 3. Type the third IF function, with the last condition and corresponding calculation if true.

1. 4. Finally, type the value to be returned if none of the three conditions are TRUE and close all three parentheses.

1. 5. Press ‘Enter’ to see the result. Grab the fill handle and drag it down to the last row.

What is the Difference Between IFS and IF in Google Sheets?

There are two main differences between the IF and IFS functions. First, IFS allows you to specify multiple conditions - without nesting - and their corresponding return values if the condition is met. Second, IFS does not have an argument to specify the value to return if the condition isn’t met.

IFS evaluates each condition in order until one is TRUE. At that point, it returns the corresponding value for that condition. Therefore, if you want to specify the return value if FALSE, you can add a final condition - TRUE - and the value you want to return if none of your “real” conditions are met. Alternatively, you can use another Google Sheets built-in function: SWITCH.

Experience Confluence: The Ultimate Team Workspace

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

You can easily work with IF statements in Google Sheets. The IF function is very flexible and can be used to evaluate different types of conditions or logical expressions. You now know how to use the IF function to test values according to a single criterion and return different results depending on whether or not they pass the test.

You also know how to use multiple conditions that all need to be TRUE by combining IF and AND. Finally, you know how to use nested IF statements to check a series of conditions in order, returning a value or calculation corresponding to the first condition that is met, as well as specifying the return value if none of the conditions are met.