The IF function in Google Sheets is a powerful tool that allows you to return specific values based on logical expressions. It is a key function that enables you to make your data aggregation, dashboard visualizations, and other spreadsheet tasks more efficient.
In this comprehensive guide, we will cover the basics of using the IF function in Google Sheets with step-by-step explanations and examples. We will also provide a video tutorial for a complete guide on using the IF function in Google Sheets.
Video Tutorial: How to Use the IF Function in Google Sheets
How Does the IF Function Work in Google Sheets?
The IF function in Google Sheets allows you to test a condition and return a specific value if it is TRUE.
The syntax and arguments of the IF function consist of the following elements:
=IF(logical_expression, value_if_true, value_if_false)
Let’s break down the syntax of the IF function to better understand it:
- The
logical_expressionis a statement that is either true or false (e.g., A4=3). - The
value_if_trueis the value returned by the function if thelogical_expressionis TRUE. - The
value_if_falseis the value returned by the function if thelogical_expressionis FALSE.
The logical_expression is a falsifiable statement (e.g., C8+C9=4). You can form the logical_expression using comparison operators (=, >, <, >=, <=, <>).
If the logical_expression is true, the IF function displays value_if_true. If the logical_expression is false, the IF function displays value_if_false.
The value_if_false is not a mandatory part of the function. The value is simply empty if you do not define it.
How to Use the IF Function in Google Sheets
Here are several examples of using the IF function in Google Sheets based on common use cases and the integration of other Google Sheets functions.
Using the IF Function to Check a Single Condition
Suppose you have a list of sales representatives, and you want to specify if each of them has reached your sales goal ($6,000).
You can use an IF function in column C to determine if your sales representatives have met the goal:
=IF(A3>6000,"Yes","No")
As you can see, the function returns “Yes” for cells whose values meet the condition.
AI + Google Sheets: Use the Formula Builder to Automatically Generate IF Formulas
You can use the free Formula Builder from Crawlan.com to automatically generate IF functions. To use the Formula Builder, simply install Crawlan.com following the prompts. The installation takes less than a minute.
Once the installation is complete, go back to the Extensions menu in Google Sheets. Crawlan.com will be available as an add-on.
Now, launch the application. Crawlan.com will run in the sidebar of your Google Sheets. Select GPT Copilot in the Crawlan.com sidebar.
Next, click on the Formula Builder.
Enter a description of a formula in the text box. For example, type: “If the value in A3 is greater than 6000, display “Yes”, otherwise display “No”.”
Then, press “Build”. The Formula Builder will automatically generate the formula based on the example.
Now, all you have to do is copy and paste your generated ArrayFormula from AI into the desired cell.
Advanced Functions
Using the IF Function to Check Multiple Conditions
Suppose you want to assign an alphabetical value to each sales representative based on their sales amount, like a grading system.
In this case, you need to check if each representative’s sales amount is greater than $3,000 and in which range it falls.
Use the IF function formula below to check these multiple conditions:
=IF(B3<3000,"F",IF(B3<7000,"D",IF(B3<10000,"C",IF(B3<13000,"B","A"))))
The formula first checks if the amount is less than $3,000. If it is, the IF function returns F. Otherwise, it moves on to the next condition (and so on).
This is just one example of how the IF function makes it easy to track and evaluate your sales team’s performance.
Using the IF Function to Perform Calculations in the Value Section
The IF function allows you to perform calculations in the value section. Here’s an example: Use the IF function to calculate the commissions of your sales representatives.
Suppose your sales representatives have the following commission scheme:
- Less than $60,000 – No commission
- $60,000 – $80,000 – 4% commission
- $80,000 or more – 10% commission
Use the IF function formula below to calculate the commission.
=IF(B3<50,0,IF(B3<80,B3*4%,B3*10%))
The function first checks if the sales value is less than 50 and returns 0 if it is.
Then, the function checks if the value is less than 80, and if it is, it returns 4% of the value. If the value is 80 or more, it is multiplied by 10% (to reflect values greater than 80).
Using the IF Function for Empty and Non-Empty Cells
You can also use IF statements with empty cells. There are two ways to use the IF statement in this manner.
First, use the ISBLANK function on this sample data set:
The example formula below checks if the cells in Total Sales (column E) are empty. Empty cells do not get the discount, while non-empty cells get a 5% discount. The complete formula should look like this:
=IF(ISBLANK(E3)=TRUE,0,0.05)
Another way to check if cell E3 is empty is with this formula:
=IF(ISBLANK(E3)<>FALSE,0,0.05)
In the inverse case, you can use these formulas to determine when cells are not empty:
=IF(ISBLANK(E3)=FALSE,0.05,0)
=IF(ISBLANK(E3)<>TRUE,0.05,0)
The second way is to use standard comparison operators with quotes. For example, this formula checks if E3 is empty:
=IF(E3="",0,0.05)
And this one does the same:
=IF(E3<>"",0,0.05)
Similarly, use quotes as an argument to return an empty cell with this formula:
=IF(E3>200,E3*0,"")
Other functions allow you to check for empty and non-empty cells, such as the SUMIF function in Google Sheets.
Using the IF Statement with Other Functions
IF OR in Google Sheets
You can also use an OR function within an IF function to test multiple logical expressions. To do this, use the OR function in the first argument:
=OR(logical_expression1, [logical_expression2, ...])
Here’s how the function works:
- The
logical_expression1is a required argument and the first logical value that the formula checks. - The
logical_expression2is an optional argument. It is the next logical value that is checked (and so on).
You can include as many logical expressions as you want, and the function will confirm if any of them are true.
For example, to group the rows of the table below by country, use the following formula:
=IF(OR(B3="East",B3="South"),"Rest of the World","Our Country")
In this case, the OR statement makes the IF statement more efficient.
IF AND in Google Sheets
The AND function also complements the functionality of the IF function. The AND function checks if all logical expressions included in a sequence are TRUE:
=AND(logical_expression1, [logical_expression2, ...])
In the example below, let’s write an IF formula that requires these two expressions to be true:
- Region = “West”
- Subscription Plan = “Enterprise”
The IF formula will look like this:
=IF(AND(B3="West",C3="Enterprise"),"Our Country","Rest of the World")
Nested IF Formula vs. IFS Function
You can use the IF statement as an argument in a larger IF function. Suppose you offer these discounts for your customers:
- Less than 100 users per subscription plan – 0% discount
- 100-199 users per subscription plan – 5% discount
- 200 or more users per subscription plan – 10% discount
The IF formula for this discount scheme would look like this:
=IF(E3>200,E3*0.1,IF(E3>100,E3*0.05,0))
In this example, a separate IF function is used as the second argument.
Now, let’s be more specific. Let’s say you offer the discounted price only to customers in the East region.
You can do this using the AND function in the formula:
=IF(AND(B3="East",E3>200),E3*0.1,IF(AND(B3="East",E3>100),E3*0.05,0))
A simpler method to write the above formula is to use the IFS function:
=IFS(condition1, value1, [condition2, value2, ...])
The syntax for the IFS function is as follows:
condition1is a required logical expression that you want to test.value1is a required value to return ifcondition1is TRUE.- List other conditions you want to check.
The complete formula with the IFS function would look like this:
=IFS(AND(B3="East",E3>200),E3*0.1,AND(B3="East",E3>100),E3*0.05)
The formula will return an N#/A error if none of the conditions are TRUE.
To avoid this, wrap your formula with the IFERROR function:
=IFERROR(IFS(AND(B3="East",E3>200),E3*0.1,AND(B3="East",E3>100),E3*0.05),0)
An Alternative to Multiple IFs: The SWITCH Function
You can use the SWITCH function in Google Sheets instead of nested IFs. The SWITCH function checks if your expression matches a list of cases. It returns a corresponding value when the expression does.
The syntax for the SWITCH function is:
=SWITCH(expression, case1, value1, [case2, value2, ...], [default])
- The
expressionis a required range of cells, cell references, an actual numerical expression, or text that you want to match your cases (or test against the criteria). - The
case1is the first required criteria to check if theexpressionmatches. - The
value1is a required record to return if thecase1criteria matches theexpression. case2,value2are optional arguments that repeat as many times as the criteria to check.
You can use ranges as the expression to test your cells against text. With the same data set, use this formula:
=ARRAYFORMULA(SWITCH(B3:B13,"West","Our Country","Rest of the World"))
In this formula, the SWITCH function checks records in each cell of column B. If the cell is “West,” the formula returns “Our Country.” Otherwise, the formula returns “Rest of the World.”
IF Function: A Useful Tool for Logical Comparisons
The IF statement in Google Sheets is a simple yet powerful tool that enhances the logical reasoning of your formulas.
With the IF statement, you can delve deeper into your analysis in Google Sheets and discover new dimensions of understanding.
And now, with Crawlan.com, you can connect Google Sheets functions like IF statements to real-time data from your business systems.
Try Crawlan.com for free now to import live data from Salesforce, HubSpot, and other business systems into Google Sheets. Crawlan.com
