What is IFS Function in Google Sheets?
The IFS function in Google Sheets is used to evaluate multiple conditions. It then returns a value based on the first true condition. It is a powerful tool where multiple conditions can be evaluated at a go without the need for nested IF statements. Thus, logical testing with complex conditions can be simplified using IFS, which returns a value that corresponds to the first TRUE condition.
Some of its benefits include avoiding the complexity of nested IF statements and improving clarity and efficiency. In the simple example below, we use the IFS function to assign a grade to a student based on their score on an exam. Enter the following formula in the cell where you need the result.
=IFS(A1>=90, “A”, A1>=60, “B”, A1>=40, “C”, A1<40, “Fail”)
Here, we test the score in A1 against four conditions and return a corresponding grade for each condition. As the value in A1 is 78, the formula returns B.
Key Takeaways
- The IFS Google Sheets function is used to evaluate multiple conditions and return a value based on the first true condition.
- The syntax of the IFS Google Sheets function is as follows:
=IFS(condition1, value1, [condition2, value2, …])
condition1, condition2…: The logical expression to evaluate.
value1, value2….: The result if the particular condition is TRUE.
- By using IFS, we can avoid the complexity of nested IF statements. It is also clear and concise to understand.
- If none of the conditions are met, you get the #N/A! error as IFS can’t handle errors on its own.
Syntax
The syntax of the IFS Google Sheets function is as follows:
=IFS(condition1, value1, [condition2, value2, …])
The function takes the arguments in pairs – a condition followed by a value.
- condition1, condition2…: The logical expression to evaluate.
- value1, value2….: The result to return if the particular condition is TRUE.
Each condition is a logical test that evaluates to either TRUE or FALSE. If a condition is TRUE, it returns its corresponding value.
How to Use IFS Function in Google Sheets?
As seen above, IFS checks for multiple conditions and returns a value corresponding to the first true condition. Instead of writing multiple IF statements, it is easy to simplify your formulas with IFS
It is extremely helpful when you have to evaluate multiple conditions. Let us look at how we can use this function in Google Sheets. There are two ways to enter the function.
- Entering IFS in Google Sheets manually
- Through the Google menu bar.
Entering AVERAGEIFS Manually
Let us understand how to use the function manually.
Step 1: To understand its usage, let us take a simple example where we have the ages of some people entered in a sheet. We have to classify them as “Minor,” “Adult,” and “Senior Citizen,” based on their age.
Enter the age details on a sheet, as shown below.
Step 2: In cell B2, enter the following:
=IFS(
Step 3: Now, enter the different conditions. Then, close the parenthesis. Each condition is separated by a comma.
=IFS(A2 < 18, “Minor”, A2 < 65, “Adult”, A2 >= 65, “Senior Citizen”)
Step 4: Press Enter. Then, drag the formula till B5 for the results for all values.
Access From the Google Menu Bar
- Choose the cell where you wish to enter the AVERAGEIFS function.
- Go to the following path: “Insert” tab => “Function” -> “Logical” -> “IFS.”
- Enter the required arguments, which are the different conditions and press Enter to get the result.
Examples
The ease of the IFS Google Sheets function is its ability to evaluate multiple conditions within a single expression. This makes it more durable and readable without the need for cumbersome nested IF statements, which can be difficult to manage as the number of conditions increases. Let us look at some of the examples below which show us how useful this function can be in simplifying complex tasks.
Example #1
In this example, let us check if a dataset contains even or odd numbers.
Step 1: Below, we have entered some values in a table..
Step 2: Enter the following formula in cell B2.
=IFS(MOD(A2,2)=0,”Even number”, MOD(A2,2)<>0,”Odd number”)
MOD(A2,2)=0 – this checks if the number in A2 when divided by 2 gives zero. If the condition is TRUE, it prints Even number.
MOD(A2,2)<>0 – this checks if the number in A2 when divided by 2 is not equal to zero. If the condition is TRUE, it prints Odd number.
Step 3: Press Enter. Now, drag the formula for the other cells as well and observe the result. We were able to get if the number is even or odd with the IFS function in a single expression.
Example #2 – Using IFS Function for Grading Systems
One of the most common uses of the IFS function is using it for grading students using the IFS function. Let us enter the students and their scores in a table as shown below.
Step 1: Enter the following formula in cell C2.
=IFS(B2>=90, “Distinction”, B2>=80, “Very Good”, B2>=60, “Average”, B2>=40,”Fair”, B2<40, “Fail”).
Here, there are 5 conditions.
We check whether the grade of the student is greater than 90, 80, 60, 40, or below 40 and give results accordingly.
90 and above means “Distinction.”
80 and above means “Very Good.”
60 and above means “Average.”
40 and above means “Fair.”
Below 40 means “Fail.”
Step 2: Press Enter. You get the grading of the student as “Very Good” as their marks are 87.
Step 3: Drag the formula and check the result for the other students.
If we must evaluate different conditions and return different results, we can use IFS to simplify it by checking all conditions without nesting multiple IF statements.
Example #3 – Combining IFS with the COUNTIF
In this example, we have the grades of some students. Let us see the overall performance of the class using COUNTIF with the IFS function. Here, if we have 5 or more students scoring above 90, the class is classified as “High Performing.” If between 3 to 5 students score above 90, the class shows “Average Performance,” and below that, it is “Poor Performance.”
Step 1: Enter all the data in a table. We have taken the data similar to the previous example.
Step 2: Enter the following formula in cell C2.
=IFS(COUNTIF(B2:B12, “>90”) >= 5, “High Performing”, COUNTIF(B2:B12, “> 90”) >= 3, “Average Performance”, COUNTIF(B2:B12, “>90”) < 3, “Poor Performance”)
Explanation:
- COUNTIF(B2:B9, “>90”): It counts how many students in the range B2:B9 score greater than 90.
- The first condition checks if five or more students are securing greater than 90. If true, it returns “High Performing”.
- The second condition checks if three to five students secure greater than 90, returning “Average Performance”.
- The third condition checks if there are less than three students securing below 90; if so, it returns “Poor Performance”
Step 2: Press Enter. You get the result as “Average Performance.” If you notice, there are only three students securing above 90; hence, according to the formula, we get the performance of the class as “Average Performance.”
COUNTIF counts the occurrences of specific conditions and IFS evaluates the given conditions and returns the corresponding result.
Important Things to Note
- While we give multiple conditions to IFS, remember that each condition is evaluated in the given order.
- When a condition evaluates to TRUE first, its corresponding result is returned.
- It is quite flexible in handling complex operations, requiring no nested IFs.
- If none of the conditions are true, you will get an error. In such a scenario, we can use a function like IFERROR and catch it.
Frequently Asked Questions (FAQs)
We use both the IFS and IF functions to carry out logical tests. However, the IFS function is a more streamlined version of the IF which can be used to check multiple conditions with ease. However, with the IF function, to check for multiple conditions you might require complex nested IFs to get the same result, thus making it difficult to understand the formula.
Syntax IFS: =IFS(condition1, value1, [condition2, value2, …])
Syntax: IF: =IF(condition, value_if_true, value_if_false)
If none of the conditions mentioned in the IFS Google Sheets function are met, it returns a #N/A error.
The function does not have any built-in mechanism for handling such errors due to a lack of match.
This can be prevented by accounting for all possible conditions so that there is no chance of not having a match. Otherwise, an error handling function like IFERROR can be used along with the IFS function for the same.
IFS is indeed similar to using multiple IF statements. Instead of using IFS, you can also use multiple nested IF statements. However, using IFS is clean, clear and concise. Multiple nested IFs are harder to maintain and execute without inaccuracy in the logic.
Download Template
This article must help understand IFS Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is IFS Function in Google Sheets. We learn its syntax & how to use Ifs function in google sheets with examples & working template. You can learn more from the following articles. –
Leave a Reply