IF

The IF formula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions.

How to use IF formula?
Examples of using IF formula
IF formula not working?
Similar formulas to IF

Usage

Use the IF formula with the syntax shown below, it has 3 required parameters:

=IF(logical_expression, value_if_true, value_if_false)

Parameters:

logical_expression (required):
A logical expression that evaluates to either true or false. This expression can be a reference to a cell containing a logical value or a formula that returns a logical value.
value_if_true (required):
The value to be returned if the logical expression is true. This value can be a number, text, logical value, or a cell reference to a value of any type.
value_if_false (required):
The value to be returned if the logical expression is false. This value can be a number, text, logical value, or a cell reference to a value of any type.

Examples

Here are a few example use cases that explain how to use the IF formula in Google Sheets.

Check if a value is greater than a threshold

The IF formula can be used to test if a value in a cell is greater than a threshold value and return a specific value if it is true or false. For example, you can use this formula to check if a student's grade is greater than or equal to a passing grade and return 'Pass' or 'Fail' accordingly.

Calculate a commission based on a sales target

You can use the IF formula to calculate a commission based on a sales target. For example, if a salesperson's sales target is $10,000, you can use this formula to calculate their commission as 5% of their sales if they meet or exceed their target and 2.5% of their sales if they don't.

Show a warning message based on a deadline

The IF formula can be used to show a warning message based on a deadline. For example, if a project is due in three days, you can use this formula to show a warning message if the project is not completed by the deadline.

Common Mistakes

IF not working? Here are some common mistakes people make when using the IF Google Sheets Formula:

Using non-logical values in the logical_expression

The logical_expression in the IF formula must evaluate to a logical value (TRUE or FALSE). If a non-logical value is used, the formula will return an error. To correct this, ensure that the logical_expression is a valid logical statement.

Not using the correct value_if_true or value_if_false parameters

The value_if_true and value_if_false parameters in the IF formula must match the expected data type. For example, if the logical_expression evaluates to TRUE, then value_if_true should be a value or formula that returns a value of the same data type. If the data types do not match, the formula may return unexpected results. To correct this, ensure that the correct data types are used for value_if_true and value_if_false.

Nesting too many IF formulas

Nesting too many IF formulas within each other can make the formula difficult to read and debug. It is recommended to use other logical functions such as AND, OR, or NOT to simplify the formula. To correct this, simplify the formula by using other logical functions.

Forgetting to close parentheses

Forgetting to close parentheses in the IF formula can cause the formula to return an error. To correct this, ensure that all parentheses are properly closed.

Using incorrect syntax

Using incorrect syntax in the IF formula can cause the formula to return an error. Ensure that the syntax follows the correct format: IF(logical_expression, value_if_true, value_if_false).

The following functions are similar to IF or are often used with it in a formula:

AND
The AND function in Google Sheets is a logical function that returns TRUE if all of the logical expressions in the argument are TRUE, and FALSE if any of the logical expressions are FALSE. It can be used to test multiple conditions and evaluate whether they are all true or not.
OR
The OR formula is a logical formula that returns TRUE if at least one of the provided logical expressions is TRUE. It returns FALSE if all logical expressions are FALSE. This formula is commonly used in conjunction with other logical formulas, such as AND, to build more complex logical statements.
NOT
The NOT formula in Google Sheets is a logical function that negates a given logical expression. If the logical expression evaluates to TRUE, the formula returns FALSE, and vice versa. This function is commonly used to reverse the result of a logical test, or to check if a value is not equal to a specific criteria.
SUMIF
The SUMIF formula is used to add up values in a range that meet a specific criterion. It can be used to sum values based on text, numbers, or dates. The formula is most commonly used in financial analysis, budgeting, and data analysis.
COUNTIF
The COUNTIF formula counts the number of cells within a specified range that meet a certain criterion. This formula is commonly used to count cells that meet a specific condition or criteria.

Learn More

You can learn more about the IF Google Sheets function on Google Support.

IF