IF Function in Google Sheets: Ultimate Guide

The IF statement in Google Sheets is a key function that lets you return certain values based on logical expressions.

The IF function tests against a condition that you define and returns a result, based on if the condition is true or false. The function helps make your data aggregation, dashboard visualizations, and other spreadsheet tasks more efficient.

This comprehensive blog covers the basics of using the IF function in Google Sheets, through examples and step-by-step walkthroughs. Also, watch our video tutorial below for a complete guide on how to use the IF function in Google Sheets.

Video Tutorial: How to Use 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 IF function’s syntax and arguments consist of the following:

=IF(logical_expression, value_if_true, value_if_false)

Let’s break down the IF function’s syntax to better understand it:

• logical_expression is a statement that is either true or false (e.x. A4=3)
• value_if_true is the value that the function returns if the logical expression is TRUE
• value_if_false is the value that the function returns if the logical expression is FALSE.

The logical_expression is a statement that is falsifiable (e.x. C8+C9=4). You can form the logical_expression with comparison operators (=, >, <, >=, <=, <>)

If the logical_expression is true, the IF function will display value_if_true. If the logical_expression is false, the IF function will display value_if_false.

The value_if_false is not a required part of the function. The value is simply blank if you do not define it.

How to Use the IF Statement in Google Sheets

Here are several examples of how to use the IF function in Google Sheets, based on popular use cases, and how to incorporate other Google Sheets functions.

Using the IF function to check a single condition

Let’s say you have a list of sales representatives, and you want to specify if each one has reached your target amount of sales ($6,000).

Using Coefficient, let’s import the dataset from Salesforce to Google Sheets below.

You can use an IF function in Column C to determine if your sales reps have reached the target:

=IF(A3>6000,”Yes”,”No”)

As you can see, the function returns “Yes” for cells with values that meet the condition.

AI + Google Sheets: Use Formula Builder to Automatically Generate IF Formulas

You can use Coefficient’s free Formula Builder to automatically generate IF Functions. To use Formula Builder, you need to install Coefficient by following along with the prompts. The install process takes less than a minute.

Once the installation is finished, return to Extensions on the Google Sheets menu. Coefficient will be available as an add-on.

Now launch the app. Coefficient will run on the sidebar of your Google Sheet. Select GPT Copilot on the Coefficient sidebar.

Then click Formula Builder.

Type a description of a formula into the text box. For this example, type: If the value in A3 is greater than 6000, print “Yes”, otherwise print “No”.

Then press ‘Build’. Formula Builder will automatically generate the formula from the first example.

Now, simply copy-paste your AI-generated ArrayFormula into the desired cell.

Advanced Functions

Using the IF function to check multiple conditions

Suppose you want to assign a letter value to each sales rep based on their sales amount, like a grading system.

In this case, you’ll need to check whether each rep’s sales amount is above $3,000, and the range that it falls into.

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. If not, it moves on to the following condition (and so on).

This is just one example of how the IF function makes it easy to track and check your sales team’s performance.

Using the IF function to do calculations in the value section

The IF function lets you perform calculations within the value section. Here’s an example: let’s use the IF function to calculate your sales reps’ commissions.

Assume your sales reps have this sales commission scheme:

• Less than $60,000 – 0% commission
• $60,000 – $80,000 – 4% commission
• $80,000 – 10% commission

Let’s use the IF function formula below to calculate the commission.

=IF(B3<50,0,IF(B3<80,B3*4%,B3*10%))

The function first determines if the sales value is less than 50, and returns 0 if so.

Then the function tests if the value is less than 80, and if true, returns the 4% of the value. If the value is 80 or higher, the value is multiplied by 10% (to reflect values over 80).

Using the IF function for blanks and non-blanks

You can also form IF statements around blank cells. There are two ways to use the IF statement in this fashion.

First, use the ISBLANK function on this sample dataset:

The sample formula below checks if the cells within Total Sales (column E) are empty. Blank cells don’t get the discount, while non-blank cells get 5% off. 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)2<>FALSE,0,0.05)

In the reverse case, you can use these formulas to ascertain when the cells are not blank:

=IF(ISBLANK(E3)=FALSE,0.05,0
=IF(ISBLANK(E3)<>TRUE,0.05,0)

The second way is to use standard comparison operators with double quotes. For instance, this formula checks if E3 is blank:

=IF(E3=””,0,0.05)

And so does this formula:

=IF(E3<>””,0,0.05)

Similarly, use double-quotes as an argument to return an empty cell with this formula:

=IF(E3>200,E3*0,””)

Other functions that allow you to check for blank and non-blank cells include the SUMIFS Google Sheets function.

Using the IF statement with other functions

IF OR on Google Sheets

You can also use an OR function within an IF function to test multiple logical expressions. To do so, deploy the OR function in the first argument:

=OR(logical_expression1, [logical_expression2, …])

Here’s how the function works:

• logical_expression1 is a required argument and the first logical value the formula checks for.
• logical_expression2 is an optional argument. It’s the next logical value that’s checked (and so on).

You can include as many logical expressions as you need, and the function will confirm if one of them is true.

For instance, to group the rows in the spreadsheet 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 on Google Sheets

The AND function also supplements the functionality of the IF function. The AND function checks if all the included logical expressions 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 within a larger IF function. Let’s say you offer these discounts for 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 will 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 get more specific. Let’s say you offer the discounted price only to clients in the East region.

You can perform this by using the AND function within the formula:

=IF(AND(B3=”East”,E3>200),E3*0.1,IF(AND(B3=”East”,E3>100),E3*0.05,0))

An easier method for writing the formula above is to use the IFS function:

=IFS(condition1, value1, [condition2, value2, …])

The syntax of the IFS function is:

• condition1 is a required logical expression that you want to test
• value1 is a required value to return if condition1 is TRUE
• List any other conditions you want to check for

The complete formula with the IFS function will 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 there is no TRUE condition.

To avoid this, wrap your formula with the IFERROR:

=IFERROR(IFS(AND(B3=”East”,E3>200),E3*0.1,AND(B3=”East”,E3>100),E3*0.05),0)

Alternative to Multiple IFs: SWITCH Function

You can employ the Google Sheets SWITCH function instead of the nested IFs. The SWITCH function checks if your expression corresponds to a case list. It returns a corresponding value when the expression does.

The SWITCH function’s syntax is:

=SWITCH(expression, case1, value1, [case2, value2, …], [default])

• expression is a required range of cells, any cell reference, an actual math expression, or a text that you would like to equal to your cases (or test against the criteria).
• case1 is a required first criterion to check the expression against.
• value1 is a required record to return if the case1 criterion is the same as the expression.
• case2, value2 are optional arguments that repeat as many times as the criteria you have to check.
• default is completely optional. It’s best to use it every time to avoid errors when your expression doesn’t meet any matches within all the cases.

You can use ranges as an expression to test your cells against a text. With the same dataset, deploy this formula:

=ARRAYFORMULA(SWITCH(B3:B13,”West”,”Our Country”,”Rest of the World”))

In this formula, the SWITCH function checks the records in every cell within column B. If the cell is “West,” the formula returns “Our Country”. Otherwise, the formula returns “Rest of the World.”

IF statement: A helpful function for making logical comparisons

The IF statement in Google Sheets is a simple but powerful tool that enhances the conditional logic of your formulas.

With the IF statement, you can expand the depth of your analysis in Google Sheets, and unlock new dimensions of insight.

And now, with Coefficient, you can pair Google Sheets functions such as IF statements with real-time data from your business systems.

Try Coefficient for free now to import live data from Salesforce, HubSpot, and other company systems into Google Sheets.