The IF function is one of the simplest tools in Google Sheets and also one of the most powerful. It works to check for a particular condition across the cells in your spreadsheet and to return one value when the condition is met and another when it is not. As a standalone tool, it is a great way to help sort data and automate a tedious input process. It’s also an essential building block to perform more complex operations in concert with other tools.
The value returned if the condition is TRUE.
The final, optional argument allows you to specify a value to be returned when the condition is FALSE. When left blank, it will simply return no value for the cells in which the logical expression is FALSE.
We’ll walk you through the basics of the Google Sheets IF function by starting with its primary purpose: producing different values based on the TRUE or FALSE result of a logical expression, such as equal to (=) or greater than (>).
Let’s say a group of ten students were given a placement test to determine which of two classes they would be taking for the semester—with students who scored 50 or higher going to Class A and students scoring 49 or lower going to Class B. We can use the IF function to automatically populate the Assignment column based on the data in the Score column.
Choose the first cell you’d like the function to populate and enter the first part of the syntax. In cases like our example, where you want to fill a whole column with results produced by a corresponding one, begin at the top.
We want the function to check the preceding cell in the row for a value greater than or equal to 50. To do so, we simply select the relevant cell, add our logical operator (in this case: >=), and the number to which it applies (50).
For the second argument, simply enter the value you want the function to return when the condition is calculated as TRUE. In cases where the value is a string of text rather than a numeral, encase it in quotation marks. In our example, we want the function to assign students to the proper class. Those who scored greater than or equal to 50 go to Class A.
In our example, we want students who scored 49 or lower—and thus did not meet the condition indicated by our logical expression—to be assigned to Class B, so we simply enter the text embedded in quotation marks. In cases where you only want a value produced for a TRUE result, you can leave this final argument blank.
Close out the syntax and hit return to see your result.
With the first row completed, you don’t need to manually enter the syntax in the remaining cells of the column. Simply accept Google Sheets’ autofill suggestion or use the mouse to select the cell containing the formula and drag to apply it to the relevant cells.
You can also use it to generate a value from cells populated by an exact string of text.
Continuing from our previous example, let’s say that the students in Class A will be participating in the school’s advanced track and we want another column to indicate whether or not a student is on that track.
Select the appropriate cell, add the equals sign (=) and enter the text in quotation marks. Because the function relies on logical operators, the cell must only include the quoted text—it will not recognize cells in which the quoted text forms only a part.
This helpful function in Google Sheets can also be harnessed as a building block to perform more complex tasks in combination with other functions and operators. Here are some of the other if functions you can use:
SUMIF: SUMIF sums data in a range which meet a specific condition.
SUMIFS: Use SUMIFS to tally numbers in a range that meet more than one condition.
COUNTIF: In Google Sheets, COUNTIF can be used to count numbers in a range which meet a given criterion.
COUNTIFS: Counts numbers in a range meeting multiple different criteria.
COUNT IF Not Empty: Learn how to count cells that are not empty.
IFS: IFS is a more elegant way to evaluate data against multiple criteria.
IFERROR: IFERROR is a tool that searches Google Sheets data for errors and replaces error messages with specified text or a blank cell.
IF THEN: IF THEN in Google Sheets allows you to evaluate data against a condition and take a corresponding action when the result is TRUE.
IF AND: Combining the IF and AND functions allows you to include multiple conditions that must be met to return a TRUE result.
IF ELSE: Google Sheets’ IF ELSE statement evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.
Multiple IF statements: Nesting multiple IF arguments allows you to for TRUE or FALSE results against more than one logical expression
IF OR: IF OR evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: By combining three different Google Sheets tools, you can search for cells that contain particular text.
AVERAGEIF: Use AVERAGE IF to calculate an average for numbers within a data range which meet provided criteria.
If you want to learn how to send emails from Google Sheets, we suggest checking out our easy step-by-step guide.
