Syntax
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression—The condition you’re checking. This must result in a TRUE or FALSE answer.
- value_if_true—The result you want if your condition is TRUE. This can be text, a number, a cell reference, or another formula.
- value_if_false—[optional][optional] The result you want if your condition is not met (FALSE). If you omit this argument, the function returns a blank value.
Sample usage
IF([Completion Date]@row ≤ [Due Date]@row, "Task finished on or ahead of schedule", "Task finished late")
Usage notes
- Always remember to enclose any text values in quotation marks, such as "Approved" or "At Risk".
- You can nest IF functions to perform multiple logical evaluations.
- A nested formula follows a single path through the formula, following each true/false result for the logical expressions one at a time. It doesn’t look at the whole formula and picks the most true path.
- When building nested formulas, you don’t need to re-check a previously assessed evaluation. For example, if you’re looking for dates on or after today, you don’t need to do this same check again at another point in the formula. That means that if you do =IF(Date@row>=Today(), "Not Overdue", IF(Date@row<Today(), "Overdue")), the second IF is unnecessary, as the dates that didn’t trigger as "True" are all less than today, and you don't need to reconfirm it.
Examples
Example 1: Simple status check
You have a Status column, and you want an Alert column to automatically flag rows that are set to At Risk.
Status | Alert (Formula in this column) |
|---|---|
Green | On Time |
Red | At Risk |
Yellow | On Time |
Formula: =IF(Status@row = "Red", "At Risk", "On Time")
How it works
When using this formula, you’re asking if the value in the current row's Status column is equal to Red. If TRUE, then the result is the text At Risk. If FALSE, then the result is the text On Time.
If you’re using symbols for status or task health, use the dropdown to see the text name assigned to each symbol. Use that as the symbol value to populate the formula. Include capitalization exactly as shown in the value names.
Example 2: Assign variable amounts within a larger calculation
You need to calculate the Total Days required for a task. The base duration is in a Duration column. You want to add a buffer to the duration that varies based on the task's importance:
- If the task is designated as a Critical Task? (checkbox is checked), you must add an extra 5 days of buffer.
- If the task isn’t critical (checkbox is unchecked), you add a standard 2-day buffer.
The IF function can return a specific value that is then used as a component in a larger formula.
Duration | Critical Task? | Total Days |
|---|---|---|
10 | ✅ | 15 |
7 | 🔲 | 9 |
5 | ✅ | 10 |
Formula: =[Duration]@row + IF([Critical Task?]@row, 5, 2)
How it works
This formula uses the IF function to determine the appropriate buffer amount and then adds it to the task's base duration.
- The formula first evaluates the IF statement: IF([Critical Task?]@row, 5, 2).
- Amount assigned:
- If the Critical Task? checkbox is checked (TRUE), the IF function returns the value 5.
- If the checkbox is unchecked (FALSE), the IF function returns the value 2.
- Final calculation: The result of the IF function (either 5 or 2) is then added to the value in the Duration column ([Duration]@row).
The value in a checkbox field is already true or false, so it can be directly interpreted by the IF function without needing an equal (=) symbol to indicate a true or false value.
Nested IF statements
When you want your sheet to check multiple conditions in sequence, you need to use nested IF statements.
Properly managing parentheses is crucial here. Ensure that each individual IF statement is correctly closed to work as expected.
A nested IF is simply an IF function placed inside the value_if_true, value_if_false, (or both) of another IF function. This allows you to ask additional questions based on the answers to the previous questions.
Nested IF formulas can ask different questions about the same data, or questions about different data entirely. This can be used to get specific, detailed summary values based on multiple evaluation questions.
Example 1 - Nested false path: Keep track of project health
You want a cell to show a specific status based on date proximity:
Red: If the task is past the due date.
Yellow: If the task is due within the next 7 days (but not yet past due).
Green: If the task is due later than 7 days from now.
Formula: =IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", "Green"))
How it works
- First IF (Red check):
- You’re asking: Is the [Due Date]@row before today?
- If TRUE: Return Red. (The formula stops here).
- If FALSE: The date is today or in the future. The function moves on to the second IF statement.
- Second IF (Yellow check):
- You’re asking: Is [Due Date]@row before or equal to seven days from today?
- If TRUE: Return Yellow. (The formula stops here).
- If FALSE: The date is more than 7 days out. The function returns the value_if_false in this IF formula: Green.
When creating a nested IF using different questions about the same values, order the IF statements in a logical order, such as smallest to largest numbers, or earliest to latest dates, to avoid ending up with dead ends in your formulas.
Example 2 - Nested true path: Identify recently and critically overdue items
You want a cell to show a specific status based on date proximity:
- Red: If the task is three or more days past the due date.
- Yellow: If the task is overdue by less than three days.
- Green: If the task isn’t due yet.
Formula: =IF([Due Date]@row < TODAY(), IF([Due Date]@row < TODAY(-3), "Red", "Yellow"), "Green")
How it works
- First IF (Overdue check)
- You’re asking: Is the [Due Date]@row before today?
- If TRUE: Proceed to second IF.
- If FALSE: The date is today or in the future. The formula returns Green. (The formula stops here).
- Second IF (Red or Yellow check):
- You’re asking: Is [Due Date]@row before three days ago?
- If TRUE: Return Red. (The formula stops here).
- If FALSE: The date is less than 3 days overdue. The function returns Yellow.
Example 3 - Nested both paths: Apply flags based on priority
We want a cell to show a specific status based on date proximity and project priority flag.
For high-priority tasks:
- Red: If the task is overdue
- Yellow: If the task is due in the next 5 days
- Green: If the task is not due yet
For regular priority tasks:
- Red: If the task is overdue
- Yellow: If the task is due in the next 3 days
- Green: If the task is not due yet
Formula: =IF([Due Date]@row < TODAY(), "Red", IF(Priority@row, IF([Due Date]@row >= TODAY(5), "Yellow", "Green"), IF([Due Date]@row >= TODAY(3), "Yellow", "Green")))
How it works
- First IF (Overdue check)
- You’re asking: Is the [Due Date]@row before today?
- If TRUE: Return Red. (The formula stops here).
- If FALSE: Proceed to second IF.
- Second IF (Assess high or regular priority):
- You’re asking: Is the Priority@row true (high priority)?
- If TRUE: Proceed to third IF.
- If FALSE: Proceed to fourth IF.
- Third IF (High-priority check)
- You’re asking: Is the [Due Date]@row due within the next 5 days?
- If TRUE: Return Yellow. (The formula stops here).
- If FALSE: Return Green. (The formula stops here).
- Fourth IF (Regular priority check)
- You’re asking: Is the [Due Date]@row due within the next 3 days?
- If TRUE: Return Yellow. (The formula stops here).
- If FALSE: Return Green. (The formula stops here).
Still need help?
Use the Formula Handbook template to find more support resources and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.