Using IF function in Google Sheets

The IF function is used in Google Sheets to run a logical test. It can only use a single condition and will return different results whether the condition is met (TRUE) or not (FALSE).

Syntax

Example

This is a simple statement. In this case, you have to be sure that value in a sheet is a number, otherwise, it will return a result that may not be what you expect.

Take a look:

The first two results are correct. The third one is not. It happens because the value in cell A4 is a string and it can’t be lower than 18.

In this case, you have to handle this exception. We want the formula to check whether a value is a number and if it’s the case, use the ISNUMBER function.

Nested IF statements

With nested IF functions you can use multiple conditions.

If a cell contains a number

First, we are going to check if the value inside a cell is a number. If this condition is met we are going to check the second condition.

If a cell contains text

In this case, you can check if a cell contains the text.

If a cell is empty

Our formula doesn’t recognize blank cells. Let’s handle empty values.

Now, each cell inside our example returns the correct message.

IF with AND and OR functions

With the IF function, you can create a formula with other logical functions, like OR, AND, NOT. By including these functions you can check multiple conditions at a time.

IF and OR

In this example, we are going to check whether students passed or failed the exams. If a student scored more than 50% on at least one test, that person passed.

You can check it using the following formula:

If the value in cell C2 is greater than 50, or value in cell D2 is greater than 50, display “YES”, otherwise display “NO”.

Enter this formula into cell E2 and autofill it for the rest of the cells in the column.

Unfortunately, one of the students failed. You can see it better if you use conditional formatting for these cells.

Select cells from E2 to E11 and go to Format >> Conditional formatting.

On the right side, in Format cells if, choose Text is exactly and type “YES”. Change color if you want, and click Done.

Click Add another rule for “NO” and use the red color.

Our example looks much better now.

IF and AND

In this case, students will have to score 50% or more on both exams if they want to pass. In this case, we are going to use the AND function.

The only thing to do here is to modify the formula in cell E2 and autofill the rest.

In this case, more than half of the students failed.

IF value is between two numbers

You can also use IF and OR functions when you want o determine whether the value is the specific range. Let’s check if a person is in the working-age (18-65).

Place this formula into cell D2 and autofill the rest of the cells:

IF range contains

If you want to check whether there is a number or text inside a range, you are not going to use the ISTEXT or ISNUMBER functions as they work only for single cells.

To count whether there is a number or text inside a range, you have to count the number of occurrences of text value or numbers.

Occurrences of text values inside a range

Occurrences of numbers inside a range

This formula checks whether there are text or numerical values and displays a message.

There are four options:

1. There are text and numbers
2. There are only text values
3. There are only numbers
4. None of the above

In this case, we have both: numbers and text in the range.