The IFS function is like several IF functions tied together. It is used when you want to show a spectrum of information with multiple possible conditions instead of simply true or false.
In Google Sheets, the IF function is used for simple if-then logic checking. It checks whether a statement in a given cell is TRUE or FALSE. You can string many IF functions together, but it's much simpler to do this using the IFS function.
This guide will teach you everything you need to know about using the IFS function, including when and how to use it, tips for things that might go wrong, and similar functions if it is not quite what you need.
The IFS function looks like this:
=IFS(condition1, value1, [condition2, ...], [value2, ...])
But what is a “condition” or “value?” Let’s break this string down into plain English.
condition1 is the first condition to be evaluated. A “condition,” in this case, can be a number, an array, or a boolean. Our examples all use “greater-than or less-than” as conditions.
value1 is the result of that first condition being “true” or “false” to the program. We are telling the computer what a “TRUE” result should look like on the sheet.
Condition2 and value2 are more of the same. The first condition that the IFS function registers as “TRUE” will show in the cell. Remember, computers think in black and white; we are making this spreadsheet work in shades of gray.
Before implementing your formula, take out any square brackets; they are placeholders. All words and letters should be in quotation marks. Numbers and cell references should not be in quotation marks. Separate all conditions and values with commas.
Note that you can add as many conditions and values as you like to the IFS function. The possibilities are limitless! However, they have to be in a certain order, as we will see later.
You need at least two sets of values and conditions to use this function.
Repeat until you have all the values necessary.
It is important to remember that, even if IFS is a versatile formula that allows for multiple answers, the programming always wants to be binary. Any computer program, including the IFS function in Google Sheets, wants to see things as “yes/TRUE” or “no/FALSE.” The IFS function makes Google Sheets perform multiple “TRUE/FALSE” checks. Remember that the first “TRUE” equation will show as the result!
There is no upper limit to how many values this formula can handle, so add as many as you like!
This function in Google Sheets is commonly used for…
For this example, we will pretend that we want to tell the former Disney CEO about the box office profits for the movies the studio produced in 2019. The Last Jedi was amazing in terms of profit margins—over 200%! How did the movies that were released in 2019 fare in comparison, and how can we use this feature of Google Sheets to tell us? Let’s go through it step-by-step.
The first step is to get all our data into Google Sheets. Once we have it all visible, we can look at what cells we need to copy into our formula.
There is nothing in cell E2 right now. This is where we will enter our formula.
Now, enter =IFS( in E2. This starts the equation.
Create your conditions: Use “>”,“<”, and “=” to create your conditions in the IFS formula. We are only concerned with the information in D2 for this part of the formula.
For example, if we want to check for a profit margin of 150% or more, we must type D2>=150%. Then, we designate “Excellent” as our value for that amount.
Repeat this step with other conditions.
It is important to note that the IFS function in Google Sheets checks conditions for being TRUE sequentially. In our case, because 150% is the largest percentage net profit in our data set, we must start with that when writing our formula and add the other percentage net profits for the other movies from highest to lowest.
If we added the formula, then any results over 50% would always get tagged with "Good", even though we want net profits over 150% to be tagged with "Excellent".
=IFS(D2>=50%,"Good", D2>=150%,"Excellent")
Thus, we must start with the highest value when writing our function. The first part that it checks as TRUE will be the one that appears!
Computers think in binary—everything is either 0 or 1, yes or no, true or false. The formula in Google Sheets is going “no, no, no, yes” to each individual equation as it filters through the conditions. It checks its equations in the order that they are written in the formula. The first TRUE equation is the answer that will appear in the cell. If it helps to think positively, that is what you should do!
The function for our chart on Disney movies looks like this:
=IFS(D2>=150%,"Excellent", D2>=100%,"Very Good",D2>=50%,"Good",D2<=50%,"Bad")
This looks good, but don’t forget to test your formula! If you get an error, figure out what went wrong, and try again. (The TRUE order mistake is very easy to make, so keep an eye out for that one!)
We’re not done quite yet. Click and drag your mouse down to apply the IFS function to all of the data in column E.
Now we’re done!
For another example, here is the IFS function being used to give letter grades in a 4th-grade classroom.
First, we will select the cell where we want our formula.
We will now enter =IFS( into C2 to start the formula.
After entering =IFS(, start adding your conditions and values (as shown above: =IFS(B2>94, “A+,” B2>89, “A,” etc.).
The final result looks like this:
=IFS(B2>94,"A+",B2>89,"A",B2>84,"B+",B2>79,"B",B2>74,"C",B2>69,"D",B2<70,"F")
Testing your formula is very important. This is what it looks like if you do it right:
Note that the IFS function starts with the highest value, A+, which requires the corresponding cell in Column B. This is because the formula checks our value-condition sets for a TRUE answer one by one, from the start of the formula down. Since Bart did not meet the criteria for A, B, C, or D, he gets the lowest grade of F, which is the only one that is “true” to Google Sheets.
But here is what happens when we reverse the conditions of the equation:
Even though the data in cell B2 has not changed, rearranging the formula was enough to register an error. Do not make this easy mistake! Start big and go smaller as you write your formula. (The formula was only altered in one cell as an example.)
Once you know your formula works, drop it down!
Few equations can do what the IFS function in Google Sheets does. The closest feature in Google Sheets is called “nested IF.” This involves stringing a bunch of IF functions together. This does the same thing with one formula. If nothing else, you will not need to type “if” as often when using the IFS function!
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
IFERROR: Replaces formula error messages with specified text or a blank cell.
IF THEN: Evaluates 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: 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.
The IFS function lets you evaluate multiple expressions and value pairs without having too many nested values. If you are a teacher or manager, this formula is your savior; it is great at ranking people whose achievements do not fall into neat, binary categories. We hope this guide has made you an expert in the IFS function.
If you want to learn how to set up email notifications in Google Sheets, we also suggest checking out our detailed guide.
