How to use the IF function in Google Sheets

IF follows an else if logic and is one of the most useful Google Sheets functions. You can use it to perform logical tests on your spreadsheets. Allowing you to carry out different actions if the test result is TRUE or FALSE.

Essentially, you tell the function: If a cell meets certain criteria, do X, if it doesn’t then do Y.

We will cover how to use nested IF statements and how to combine it with other functions like AND, and OR.

How to use the IF function in Google Sheets

1. Syntax
2. How to use Google Sheets IF function
   1. Example 1: Regular IF statements
   2. Example 2: IF statements with a calculation as the result
   3. Example 3: Nested IF statements
   4. Example 4: IF functions with AND & OR functions
3. IFS Function

Syntax

• logical_expression – the expression to evaluate as TRUE or FALSE.
• value_if_true – the value that the function returns if logical_expression is TRUE. This can be a number, text, or even another formula.
• value_if_false –  the value that the function returns if logical_expression is FALSE. This can be a number, text, or even another formula. If not included, it will return a blank value.

This flowchart explains the internal logic of the IF function.

How to use Google Sheets IF function

Example 1: Regular IF statements

The easiest way to understand the IF function is to see it in action.

Take a look at the following sample data (columns A through E). In column F you can see some common variations of the function.

You will notice that I have experimented with Boolean values (TRUE or FALSE), dates, numbers, and also text.

There are two rows for each example, to show both results of the function if the value is true or false. To get started, let’s go over the formula in the first two rows.

In simple terms, I am telling the function “If the value in cell E2 is ‘Yes’ the output should be ‘On Campus’, otherwise it should be ‘Not On Campus’ “.

By taking a look at all of the examples, you will get an idea of the versatility of the IF function. You can use it with just about any other function: you can see the DATE function in rows 6 & 7, and the LEN (length of a text string) function in rows 8 & 9.

It can also use a variety of logical tests to evaluate whether an expression is TRUE or FALSE. The logical tests that you can use can be seen below.

Example 2: IF statements with a calculation as the result

You can also use an IF statement to return a mathematical calculation.

In the following example, I’m evaluating the final grade of a group of students, based on whether or not they receive bonus marks. If they do, their final grade will increase by the percentage indicated, and if they don’t, their final grade will stay the same.

Notice that in this case, the value_if_true is actually a calculation that the function must perform.

Example 3: Nested IF statements

An IF formula within another IF formula is known as a nested IF statement. You can see the logic in the diagram below.

As this flowchart shows, there’s a nested IF within the value_if_false. It could have been nested within the value_if_true.

In my example, if the IF test for Expression-1 is FALSE, the function goes to the next test: the nested IF function. Accordingly, it returns either B or C based on whether the test for Expression-2 is TRUE or FALSE.

This diagram shows a single nested IF statement. But you can also nest multiple IF statements in Google Sheets. This means adding multiple IF functions in a hierarchical fashion.

The example below uses an IF statement to evaluate how much experience each worker has.

Again, in simple terms, I’m telling the function “Evaluate if B2 is 0, if it’s TRUE output ‘No experience’, if it’s FALSE continue to the next IF statement. Evaluate if B2 is less than 4, if it’s TRUE output ‘Some experience’, if it’s FALSE output ‘Lots of experience’ ”.

Example 4: IF functions with AND & OR functions

The AND and OR logical functions in Google Sheets can be used in combination with the IF function. These two expressions are fairly self-explanatory:

• AND evaluates multiple criteria and returns TRUE if all of them are TRUE.
• OR evaluates multiple criteria and returns TRUE if one of them is TRUE.

IF with AND function

Here’s an example of an IF function that also uses an AND function.

The AND function enables testing against two criteria in my logical test: a minimum value and a maximum value. By doing so, I can separate the ages into segments: “Early 20s”, “Mid 20s”, and “Late 20s”.

Notice that I’ve also included “Not in their 20s” as the value_if_false at the end of the formula. This ensures that data that doesn’t fit the established criteria (ages not in the 20s) will still have an output that makes sense.

Check out our post on the AND function for a complete overview including other use cases.

IF with OR function

I’ll now show an example using the OR function within an IF statement. This evaluates the overall experience of workers: anyone above the age of 25 or with more than 4 years of work experience, is considered “experienced”.

Note the difference between this example and the previous one. Here, I want the value to be TRUE if either criteria is met, so I use the OR function. If I want the value to be TRUE only if all criteria are met, so I used the AND function.

Check out our article on the OR function for a full explanation and more examples.

IFS Function

The IF function helps you apply logical functions to your spreadsheet data through simple TRUE or FALSE evaluations. Nested IFs can be used when you want to test data against multiple criteria, but make formulas hard to read.

Luckily there’s an alternative to nested IF statements: the IFS function in Google Sheets.