Google Sheets
by Hady ElHady | Mar 13, 2024
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.Type the IF function in the ‘Action’ column, followed by the AND function.
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”.
3. Add a comma and the second condition: the value in column C must be equal to “Yes”.
4. Close the parenthesis for the AND function and add a comma.
5. Type the value you want the function to return if both conditions are met: “Send email”.
6. Add a comma and type the return value if one or both conditions are not met: “None”.
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 Share Only One Tab in Google Sheets
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MORE →
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. In the “Bonus %” column, type in the IF function and the first condition.
2. For the second argument, type the value you want the function to return if the first condition is true.
3. For the third argument, type the next IF function and use the second condition as the first argument.
4. For the second argument of the second IF function, type the corresponding value to be returned if the second condition is true.
5. For the third argument, type the last IF function and the third condition as its first argument.
6. For the second argument of the third IF function, type the last condition and corresponding value if true.
7. Finally, type the value to be returned if none of the three conditions are true.
8. Close all three parentheses and press ‘Enter’ to see the result. Grab the fill handle and drag it down to the last row.
How to Password-Protect a Google Sheet?
If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet
READ MORE →
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. In the “Bonus Amount” column, type in the IF function. Type the first condition and the corresponding calculation, each followed by a comma.
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.
3. Type the third IF function, with the last condition and corresponding calculation if true.
4. Finally, type the value to be returned if none of the three conditions are TRUE and close all three parentheses.
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.
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.
How to Password-Protect a Google Sheet?
Have you ever wondered if your online documents are secure? Even if you save them in your Google Drive, adding extra protection to those with more sensitive data is always a good idea. To address this issue, we recently spoke about how you can lock data in Google Sheets by giving or restricting editing permissions […]
Google Data Studio Tutorial For Beginners
How to Create and Use a Pivot Table in Google Sheets?
Analyzing large datasets for decision-making can be a daunting challenge, especially if we are relying wholly on a basic spreadsheet table view. This is where the potential of pivot tables is immeasurable.Google Sheets allows you to create pivot tables to achieve better and more efficient data analysis. Users can adopt a pivot table for any […]
How to Make a Schedule in Google Sheets?
While Google Calendar is used to plan their weekly schedule, if the number of tasks and events to include is large, adding them to the calendar will only increase an already vast workload. Choosing to make a schedule in Google Sheets over any other calendar app is not only better thanks to its data capacity; […]
How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)
When working with large datasets, the information tends to be spread out among different spreadsheets. In how to do VLOOKUP in Excel with two spreadsheets we explain the many benefits of using the VLOOKUP function to easily transfer information between sheets and workbooks in Excel.Google Sheets also allows you to use the VLOOKUP function to […]
Try Sheetgo
Automate your process on top of spreadsheets
SIGN UP FOR FREE
Try Sheetgo
Automate your process on top of spreadsheets
SIGN UP FOR FREE
