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).
IF(logical_expression, value_if_true, value_if_false)
=IF(A1 < 18, "You are underage", "You are an adult")
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(ISNUMBER(A2), IF(A2 < 18, "You are underage", "You are an adult"), "The value is not a number")
If a cell contains text
In this case, you can check if a cell contains the text.
=IF(ISNUMBER(A2), IF(A2 < 18, "You are underage", "You are an adult"), IF(ISTEXT(A2), "Enter a number not text", "Can't recognize the value"))
If a cell is empty
Our formula doesn’t recognize blank cells. Let’s handle empty values.
=IF(ISNUMBER(A2), IF(A2 < 18, "You are underage", "You are an adult"), IF(ISTEXT(A2), "Enter a number not text", IF(ISBLANK(A2),"The cell is blank", "Can't recognize the value")))
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).
=IF(OR(C2<18,C2>65),"Working age","Minor or retiree")
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.
=IF(AND(COUNTIF(A2:A11,"*")>0, COUNT(A2:A11)>0), "There are text and number in the range", IF(COUNTIF(A2:A11,"*")>0, "There is text in the range",IF(COUNT(A2:A11)>0, "There is a number in the range", "There isn't any text or numbers in the range")))
There are four options:
- There are text and numbers
- There are only text values
- There are only numbers
- None of the above
In this case, we have both: numbers and text in the range.