The IF function evaluates a logical statement and returns one value if the statement is true, and another value if the statement is false. IF statements are one of the most basic building blocks incorporating logic into your Google Sheets. Adding logic to your Google Sheets lets your sheet automatically "decide" when to use different calculations or display different values.
For example, say you have a cell which displays the temperature. If the number in the cell is above 32, your sheet displays the word "warm". On the other hand, if the temperature is 32 or below, your sheet displays the word "cold".
The IF function has two required arguments (function inputs inside the parenthesis separated by commas) and one optional argument.
=IF(logical_statement, value_if_true, [value_if_false])You may be wondering how to write a logical statement in Google Sheets that returns True or False for the first part of your IF statement. To write a logical statement you compare two values using one of six logical comparison operators from the table below.
You can use these comparison operators to compare text values, dates, numbers, and more.
| OPERATOR | MEANING |
| = | Equal to |
| <> | Not equal to |
| < | Less than |
| > | Greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
See the table below for some examples of logical statements in Google Sheets.
| LOGICAL STATEMENT | RESULT |
| 1=1 | True |
| 1=2 | False |
| "Hello" <> "Goodbye" | True |
| 5<>5 | False |
| 1/1/2021 > 1/1/2020 | True |
| 5>6 | False |
| 10<20 | True |
| 5/1/2020 < 4/1/2020 | False |
| 10<=20 | True |
| 10<=10 | True |
| 5>=1 | True |
| 5>=6 | False |
Now that you know how to write logical statements, the hard part is out of the way. Now you just need to tell your formula what to return if the statement is true and what to return if the statement is false. Below is an example of a Google Sheet where we display information about a hypothetical employee named Susan.
| A | B | C | |
|---|---|---|---|
| 1 | Name: | Susan | |
| 2 | Age: | 45 | |
| 3 | Savings ($): | 600,000 |
Remember that the IF function in Google Sheets has three arguments:
=IF(logical_statement, value_if_true, [value_if_false])Let's try writing an IF statement to display whether the employee is retired. We'll say that workers retire at age 65.
=IF(B2>=65, "Retired", "Not Retired")The IF statement above will display the text "Retired" if the number in the age cell is 65 or above, and will display the text "Not Retired" if the employee's age is below 65. In the example of Susan above, the IF function would display the text "Not Retired". We could also omit the third argument and just write the following:
=IF(B2>=65, "Retired")This IF statement will display the text "Retired" if age is 65 or above, and will simply show nothing if the age is below 65.
Let's try writing an IF statement which tells us when employees have enough money for retirement. We'll say that employees want to save $500,000 for retirement.
=IF(B3>=500000, "Ready to retire!", "Keep working!")The IF statement above will display "Ready to retire!" if the employee's savings are $500,000 or higher, and will otherwise display the text "Keep working!" In the example of Susan above, the IF function would display the text "Ready to retire!"
Sometimes we may want to write an IF statement which checks whether one condition is true AND a second condition is true. Or maybe we want to check whether one condition is true OR a different condition is true. This is where compound logical statements come in handy.
Pretend from the example above that a worker is ready to retire only if they are at least 65 years old AND they have $500,000 in savings. We can use the AND function in Google Sheets to determine whether both conditions are true. The AND function will return true only if ALL of its arguments are true.
=AND(condition_1, [condition_2], ...)We can check whether the employee is at least 65 AND has at least $500,000 in savings with the following formula.
=IF(AND(B2>=65, B3>=500000), "Ready to retire!", "Keep working!")Or perhaps if a worker has saved $1,000,000 they are ready to retire, regardless of age! We can use the OR function to determine whether the worker is at least 65 years old OR has savings of at least $1,000,000. The OR function returns true if one or more of its conditions are true.
=OR(condition_1, [condition_2], ...)Let's check whether the worker is at least 65 or has $1,000,000.
=IF(OR(B2>=65, B3>=1000000), "Ready to retire!", "Keep working!")In the examples above, we saw how an IF statement in Google Sheets can return simple text based on a logical statement. But the IF function can perform much more complex calculations based on numbers, text, dates, and other types of data.
For example, say you run a business where you match your employees' full 401K contributions up to 6%. In other words, if the employee contributes 6% or less of her income to her 401K, you contribute the same amount. If she contributes more than 6%, you continue contributing 6%.
| A | B | C | |
|---|---|---|---|
| 1 | Name: | Susan | |
| 2 | Monthly Salary ($): | 10,000 | |
| 3 | Monthly Contribution ($): | 500 |
You can calculate the amount to match with the following IF statement:
=IF((B3/B2) <= 0.06, B3, B2*0.06)This IF statement says if the contribution divided by the salary is less than or equal to 6%, then match the employee's contribution (the amount in cell B3). Otherwise, if the employee is contributing more than 6% of their salary, then simply contribute 6% of the salary.
Now say your business matches 50% of employee contributions up to 6%. So if the employee contributes less than 6% of their salary, you contribute half of that amount. If the employee contributes 6% or more, then you contribute 3% (half of 6%).
=IF((B3/B2) <= 0.06, B3*0.5, B2*0.03)This IF statement says if the contribution is less than or equal to 6% of the salary, then match half of the contribution. Otherwise, if the contribution is greater than 6%, then contribute 3% of the employee's salary.
Sometimes you will see a nested IF statement, or nested IF function, which is one or more IF statements inside another IF statement. It sounds confusing but is pretty straightforward once you have a good handle on the IF function. Simply start with the outermost IF statement and follow the path of logic.
Say you run an animal shelter that houses dogs and cats, and you feed all the animals each day according to the following rules:
You have information on each animal in a Google Sheet.
| A | B | C | |
|---|---|---|---|
| 1 | NAME | SPECIES | WEIGHT (LBS) |
| 2 | Morris | Cat | 23 |
| 3 | Nigel | Dog | 18 |
| 4 | Dorris | Cat | 15 |
| 5 | Olive | Dog | 45 |
You might write a nested IF statement to determine how many scoops of food each animal gets. Let's start by writing the IF function for row 1.
=IF(B2="Cat", 2, IF(C2<30, 3, 4))This IF statement says IF the species is "Cat" then return 2 (because cats get 2 scoops of food). Otherwise, IF weight is less than 30 return 3 (because dogs under 30 lbs get 3 scoops). Otherwise, return 4 (because dogs 30 lbs and above get 4 scoops).
Notice how the word "Cat" is enclosed in quotation marks, while the numbers are not. In Google Sheets text must be enclosed in quotation marks. Also note that the first IF function starts with an equals sign, while the inner IF function does not. When writing formulae only the first function must start with an equals sign, and all subsequent functions don't require an equals sign.
Sometimes in your IF statements you will want to write a logical statement which compares two text values. Using the "equal to" operator and the "not equal to" operator are pretty straightforward.
"Hello" = "Hello" would return true.
"Hello" <> "Goodbye" would also return true.
However, you can use the other comparison operators on text as well. It may seem silly to say "Hello" is greater than "Goodbye," but in Google Sheets we can compare text values by their alphabetical order. Since the letter B comes after the letter A, we say that B is greater than A, and A is less than B. As such, we can compare text values alphabetically.
"Hello" > "Goodbye" would return true, because G comes before H in the alphabet.
"AAA" > "B" would return false because A comes before B, so A is less than B.
The IF function has countless uses in making powerful spreadsheets and models. Mastering IF functions and other logical functions in Google Sheets will enable you to start making smart spreadsheets with complex calculations and logic.
The best way to master a new function is to get hands-on practice using the function and writing formulas. Try some hands-on practice problems with the IF function now!