If-then statements are a helpful logical tool for sorting and refining data in Google Sheets. Luckily, Google Sheets’ IF function makes it easy to format and apply such logical statements across a whole spreadsheet. All you need is a logical expression—the “if”—and the values you’d like entered in cases where the statement is true or false—the “then.”
=IF(logical_expression, value_if_true, [value_if_false])
Where:
logical_expression=
A number or an exact string of text and a logical operator. Google Sheets allows the use of six different logical operators that you can use to formulate your logical statement. These include:
value_if_true=
The input made to the cell when the logical expression is TRUE. This can be a number, a string of text, or a mathematical calculation.
value_if_false=
The input made to the cell when the logical expression is FALSE. Once again, numbers, text, and equations are all viable options.
Let’s look at some examples to more concretely illustrate how these types of logical statements work with Google Sheets’ IF function. We’ll explore two different scenarios: one featuring a logical expression involving numerals that results in two possible text values, and another in which true and false results to a logical expression produce a value from a mathematical equation.
You can follow along by clicking here to access our sample spreadsheet.
Imagine you work for a charity that offers different membership levels based on the amount of monthly donations, with donors who give more than $100 per month included in the “Giving Circle” and those donating less recognized as “Contributors.” We’ve got a Google Sheets spreadsheet listing members alongside their monthly contribution. Let’s use the IF function to train Google Sheets to assign each member to the right level.
Select the first relevant cell in the column and type in the beginning of the IF function syntax.
In our example, we want to distinguish between monthly contributions of $100 or more and those that are smaller. Simply select the relevant cell from the Monthly Contribution column and type the symbol for greater than or equal to (>=) and the number 100.
With our logical expression identifying numbers equal to or greater than 100, we want our TRUE result to fill the corresponding cell in the Level column with “Giving Circle.”
Next, enter the value for the function to input in the cell in cases where the logical expression is FALSE. In this case, a member donating less than $100 per month is labeled a “Contributor.”
Close parenthesis to complete the syntax and hit enter to see your result.
Once you have gotten your result, you can simply select the completed cell and drag it down the column to fill the remaining cells with the appropriate formula. Google Sheets will take care of the rest!
With Google Sheets’ IF function, you can also create statements that include a mathematical calculation.
Say the charity uses a payment processing platform that keeps a percentage of each transaction as a fee, with rates varying depending on the total charge: 4% for amounts under $100 and 2% for charges equal to or more than that. The Monthly Contribution column reflects each member’s nominal pledge, or the gross amount, but the charity also wants the spreadsheet to indicate the net deduction taken by the payment processor. The IF function can automate this calculation across an entire column. Let’s see how
After you have created a new column for the Net Deduction, choose the first cell to begin entering the formula.
Here, we select the relevant cell—in our case, B2, since the Monthly Contribution will determine the percentage deducted—and add the greater-than-or-equal-to operator (>=) and the numeral $100.
Monthly charges of $100 or more will meet the logical expression and thus receive a deduction of 2%. We simply select the cell, add the multiplication symbol, and 0.02.
Monthly contributions below $100 will produce a false result and should receive a deduction of 4%. Select the cell in the Monthly Contribution once again, add a multiplication symbol and the relevant amount, here.04.
Closing the parentheses completes and triggers the function, producing the result.
Once again, we can simply select the cell and drag down to fill, allowing the spreadsheet to make the necessary adjustments for every parallel cell in the Monthly Contribution column.
IF: Allows you to check for specific conditions across a dataset
SUMIF: Use to sum numbers if they meet a certain condition..
SUMIFS: Sums data from cells that meet multiple criteria
COUNTIF: count data if it fulfils certain criteria
COUNTIFS: Count data that fulfils two or more criteria.
COUNT IF Not Null: Count cells if they contain data
IFS: Allows you to check for multiple if conditions in a single statement
IFERROR: Allows you to output values when an Error in a formula occurs
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: Evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.
Multiple IF: Learn how to use multiple if statements in a single formula
IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: Returns cells that contain a particular text.
AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria.
If you want to learn how to send emails based on dates in Google Sheets, we also suggest checking out our detailed guide.
