IF Function and Multiple IF Statements in Google Sheets

The IF function in Google Sheets is the next logical step up from the basic functions such as the SUM or AVERAGE functions. Learning this function will broaden your skills in spreadsheets as it allows you to evaluate values using comparison operators such as ‘equal to’, ‘greater than' or ‘less than’, to get your desired result.

Conditional logic is essentially checking if something is TRUE or FALSE. This means that if something is TRUE, then one thing will happen, but if it’s FALSE, something else will happen. You’ve probably heard of the term “If this then that” or IFTTT; we will explore how this works in Google Sheets with the IF function.

Once you’ve mastered the IF function, it will unlock a variety of opportunities to dynamically manipulate your data in ways you’ve never been able to before, and with minimal effort.

Syntax

The syntax is not as scary as you think; let’s break it down.

IF(logical_expression, value_if_true, value_if_false)

• Logical_expression This is the part in the function we want to check. It’s typically a reference to a cell which will contain a value or a string of text.
  

• Value_if_true When the logical expression above is TRUE, this is what we will return in the results.
  

• Value_if_false When the logical expression above is not TRUE, in other words, it’s FALSE; this is what we will return in the results.

IF Function

Examples are always essential to demonstrate how things work, so let’s dive in and take a look at how we can use a simple IF function on some data.

First, using the table above for sales by an agent, we’re going to calculate the most straightforward IF statement, which will be simply checking if a bonus should be paid or not based on a specified condition.

Logical Expression

We’ll begin by typing out the function; you can see in the image below the first thing we need is the Logical_expression.

=IF(

For the Logical_expression, we’re going to check if a value in the total column is greater than a value that we decide; we’ll select the cell next to the formula (H3) for the value we want to check.

Comparison Operators

Using comparison operators, as shown below, we can compare numeric values, date and time values, and text values with an IF function.

• = Equal to

• <> Not Equal to

• < Less than

• > Greater than

• <= Less than or Equal to

• >= Greater than or Equal to

The comparison operator considers the value on the left-hand side and compares it to the value on the right; the output will either be TRUE or FALSE.

We will compare (H3), which has a value of £5,589 to 5000. It doesn’t matter that the 5000 doesn’t have a thousand separator (5,000); when used in functions, you don’t need them.

In this example, we’re asking the function to check if £5,589.00 (H3) is greater than (>) 5000.

=IF(H3>5000

Value if True

£5,589.00 is greater than 5000; therefore, the condition is TRUE, so for the next part of the function, we need to declare what will happen if the result is TRUE: Value_if_true

=IF(H3>5000,"Yes"

We will simply declare the TRUE condition to return a string of text that says “Yes”. When we see this result, we’ll know that a bonus should be paid because the total sales for this agent were greater than our threshold of 5000. Remember that you can make this 5000 at any value you wish.

Value if False

Lastly, we need to declare what should happen if the value in the H3 cell is not greater than 5000; in other words, it’s FALSE. To do this, we need to move into the Value_if_false part of the function, where we’ll just return a string of text to say “No”. Then we can close the parentheses to complete the formula.

=IF(H3>5000,"Yes","No")

When we see this result, we’ll know that a bonus should not be paid because the total sales for this agent were not greater than our threshold of 5000.

Remember to separate each argument in the function with commas and wrap the whole thing in parentheses.

=IF(logical_expression , Value_if_true , Value_if_false)

Results

Because £5,589 is greater than 5000 we saw the result as “Yes” in our results for this row.

If we copy this formula down to the cells below, you will notice that any value in the total column that is less than £5,000 shows a ‘No’ in the bonus column, and conversely, values above £5,000 show as ‘Yes’

Using Calculations in an IF Statement

Using a string of text as a result is the most straightforward method when working with the IF function; however, it’s possible to make this more practical by incorporating further logic to the Value_if_true or Value_if_false results.

In this example below, the logical expression is precisely the same as before =IF(H3>5000, but now we’re performing a mathematical calculation for the Value_if_true part.

=IF(H3>5000,H3*10%,0)

We’re calculating 10% of the total sales value as our result for the Value_if_true H3*10% and returning a zero for the Value_if_false,0)

Using the IF function like this will allow you to generate a number dynamically, which is much more efficient and pragmatic.

Multiple IF Statements

The possibilities don’t end with IF statements; you could incorporate multiple nested IF statements to compare the Logical_expression (in our case, an agent's total sales) to a range of predefined bonus amounts in a separate table.

In this example, each part of the formula is broken into four steps, as highlighted above; let’s determine them in turn to understand the logic.

Step 1

=IF(H5<H13,0, This part evaluates if the value of £3947 in cell H5 is less than £3000 in the lower table H13. If this is true, zero will be returned (a Zero bonus will be applied). This is the Value_if_true argument. If this is FALSE, we move into step 2 for the Value_if_false argument.

Here’s the interesting part, because we move into another IF statement for steps 2, 3 and 4, we skip the Value_if_false argument, so to speak. The next IF statement begins again by evaluating if this is TRUE and so on.

Step 2

IF(H5<H14,I13, This part evaluates if the value of £3947 in cell H5 is less than £4000 in the lower table H14. If this is true, a predefined bonus of £150 will be returned from this lower table (column 2) I13. This is the Value_if_true argument. If this is FALSE, then we move into step 3 for the Value_if_false argument just as before.

Step 3

IF(H5<H15,I14, This part evaluates if the value of £3947 in cell H5 is less than £5000 in the lower table H15. If this is true, a predefined bonus of £250 will be returned from this lower table (column 2) I14. This is the Value_if_true argument. If this is FALSE, we move into step 4 for the Value_if_false argument.

Step 4

IF(H5>H15,I15) Finally, the value of £3947 is evaluated by checking if the value in cell H5 is greater than £5000 in the lower table H15. If this is true, a predefined bonus of £400 will be returned from this lower table (column 2) I15. This is the Value_if_true part.

Summary

Essentially, there isn’t a FALSE argument. We don’t need a false response in this scenario since each condition has covered every possibility.

First, we checked if the value was less than £3000, then less than £4000, then less than £5000, and finally, if the value was greater than £5000, therefore the answer must fall into one of these categories.

Admittedly, if an agent managed to achieve £10,000 or more in sales, they would still only get the same bonus as someone who only got, say, £5,001 in sales, but you could continue with additional conditions to cover all possible outcomes using this approach.

Alternatively, another way to achieve the same thing with a FALSE condition would be to keep everything the same except the last condition (step 4); we can drop this and replace this with a FALSE argument. This means that if the first three IF statements were FALSE, this last Value_if_false would be returned. This £400 bonus from cell $I$15 covers any value greater than £5,000.

=IF(H6<$H$13,0, IF(H6<$H$14,$I$13, IF(H6<$H$15,$I$14,$I$15)))

You can connect as many IF statements as you need to, and if none of them evaluates to TRUE, it will return your defined Value_if_false.

IFS Function

There is a more modern way to approach the multiple IF statement technique now using the IFS function to check for multiple criteria. We’ll explore this in more detail in another tutorial, but you can see in this example below it’s less complicated as we don’t need to nest multiple IF statements.

Each condition works as a pair; Condition and Value. In this example, H4<$H$ is the condition, and 0 is the value.

=IFS(H4<$H$13,0

Conclusion

The IF or IFS functions can be extremely beneficial if you need to use conditional logic to evaluate your data.

It might take some time to get comfortable using them, and although you won’t want to hear it, you’re bound to bump into problems along the way. Slow and steady will win the race; it just takes practice.

The great thing about many functions in Google Sheets is that you can combine them to have IF, AND, OR and anything else you can think of in one formula. Once you’ve mastered the IF function, your imagination will only limit the possibilities. You’ll wonder how you ever got by without them.

Here is the IF Function Google Sheet used in this tutorial; feel free to make a copy and play around with it. Try adding more bonus tiers and adapting the IF functions to take more scenarios into account with multiple IF statements.

IF you can understand THIS, THEN you can do THAT : )