This guide covers everything you need to know about the Google Sheets IFS function, including its definition, syntax, use cases, and how to use it.
What is the IFS Function? How Does It Work?
The IFS function in Google Sheets evaluates multiple conditions and returns either TRUE or FALSE based on the conditions you specify. It’s particularly useful when you have more than one condition that needs to be met for specific actions to be carried out.
For example, in a sales report, you might use the IFS function to categorize sales figures into different levels like “low”, “medium”, and “high”. The IFS function can evaluate each sales figure against the conditions you’ve set for each category and return the relevant category.
The beauty of the IFS function is that it can evaluate multiple conditions without having to nest several IF functions together.
The order of your conditions matters. If you have overlapping conditions, the IFS function will return the value for the first true condition it encounters and won’t check the remaining conditions. So, always structure your conditions carefully when using the IFS function.
IFS Syntax
The syntax and arguments for the function are as follows:
IFS(condition1, value1, [condition2, value2], …)
Where:
- Each ‘condition’ argument in the function is a logical expression that you want to test. These conditions are tested in the order they are written. As soon as a condition is found to be TRUE, the function will return the corresponding value and stop evaluating.
- Each ‘value’ argument is the result that the IFS function will return if the corresponding condition is TRUE or FALSE.
Examples of How to Use the IFS Function
Here are some practical examples of how to use the IFS function in Google Sheets:
Example #1: Basic IFS Function
Let’s start with a simple example. Suppose you have a list of students and their grades in a spreadsheet. You want to classify them as ‘Pass’ or ‘Fail’ based on whether their grade is above or below 50. You can use the IFS function to do this.
In cell B2, you would enter the formula: =IFS(A2>=50, “Pass”, A2<50, “Fail”). This formula says: if the value in cell A2 is greater than or equal to 50, return “Pass”; if it’s less than 50, return “Fail”.
Example #2: Multiple Conditions
The IFS function can also handle multiple conditions. Let’s say you want to classify the students not just as ‘Pass’ or ‘Fail’ but also ‘Excellent’ if their grade is above 90. The formula would be: =IFS(A2>=90, “Excellent”, A2>=50, “Pass”, A2<50, “Fail”).
Example #3: Handling Errors
What if there’s an error in your dataset, like a missing grade? The IFS function will return an error if none of the conditions are met. To handle this, you can add a catch-all condition at the end of your IFS function. For example: =IFS(A2>=90, “Excellent”, A2>=50, “Pass”, A2<50, “Fail”, TRUE, “No Grade”).
Why Is IFS Not Working? Troubleshooting Common Errors
If you are using IFS in Google Sheets and it’s not working as expected, you might be experiencing one of the following common errors. These errors can be caused by a variety of issues, but with the right troubleshooting techniques, they can often be resolved fairly quickly.
#VALUE! Error
Cause: The #VALUE! error usually appears when one or more of the logical expressions you have inputted into your IFS function do not evaluate to TRUE or FALSE. This can happen if, for example, you’re trying to compare a text string with a number or if you’ve inputted a logical expression that Google Sheets can’t understand.
Solution: Check your logical expressions to ensure they are correctly formatted and capable of evaluating to TRUE or FALSE. If you’re comparing different data types, make sure they’re compatible. For example, you can’t directly compare a text string and a number, but you can first use the VALUE function to convert the text string to a number.
#N/A Error
Cause: The #N/A error typically indicates that none of the logical expressions in your IFS function have been evaluated to TRUE. This could be because you’ve inputted incorrect conditions or because the data you’re evaluating doesn’t match any of your conditions.
Solution: Review the conditions you’ve inputted into your IFS function to ensure they’re correct. You might need to modify your conditions to accurately match your data.
#REF! Error
Cause: The #REF! error often occurs when you’re referencing a cell that doesn’t exist, such as a cell outside the current sheet’s range.
Solution: Check the cell references in your IFS function to confirm that they all point to cells that exist within your current sheet. If you’re referencing cells from a different sheet, ensure you’ve inputted the sheet name correctly.
#DIV/0! Error
Cause: The #DIV/0! error will appear when one of your logical expressions attempts to divide by zero, which is mathematically undefined and therefore, results in an error.
Solution: Review your logical expressions to find any division operations. If you find one, ensure the divisor (the number you’re dividing by) is not zero. If it’s a cell reference, check the corresponding cell to ensure it contains a non-zero value.
#NUM! Error
Cause: The #NUM! error usually occurs when one of your logical expressions creates a result that’s too large or too small for Google Sheets to handle.
Solution: Check your logical expressions for any operations that could create extremely large or small numbers. If you find one, consider modifying your expressions to avoid this issue. If it’s a cell reference causing the problem, check the corresponding cell to ensure it contains a reasonable value.
Using IFS With Other Google Sheets Functions
Combining the IFS function with other Google Sheets functions can provide even greater utility, allowing you to create more complex and powerful formulas. Here are some examples of how you can use IFS with other functions.
With COUNTIF
Usage: The COUNTIF function in Google Sheets is used to count the number of cells that meet a specific criterion. You can use it with the IFS function to create a condition-based count.
Example: Let’s say you have a list of students and their grades in a class. You want to count the number of students who scored above 80. Here’s how you can use IFS and COUNTIF together:
=IFS(COUNTIF(A2:A10,”>80″)>0, “High Achievers: “&COUNTIF(A2:A10,”>80″), COUNTIF(A2:A10,”<=80″)>0, “Need Improvement: “&COUNTIF(A2:A10,”<=80”))
This formula will return “High Achievers: X” if any students scored above 80 and “Need Improvement: Y” if there are students who scored 80 or below, where X and Y are the counts of students.
With SUMIF
Usage: The SUMIF function is used to sum the values in a range that meets a certain criterion. When used with the IFS function, it can provide a condition-based sum.
Example: Imagine you have a list of products sold and their respective quantities. You want to calculate the total quantity sold for a specific product. Here’s how you can use IFS and SUMIF together:
=IFS(SUMIF(A2:A10,”Product A”,B2:B10)>0, “Total for Product A: “&SUMIF(A2:A10,”Product A”,B2:B10), SUMIF(A2:A10,”Product B”,B2:B10)>0, “Total for Product B: “&SUMIF(A2:A10,”Product B”,B2:B10))
This formula will return “Total for Product A: X” if the total quantity for Product A is greater than 0 and “Total for Product B: Y” if the total quantity for Product B is greater than 0, where X and Y are the total quantities.
With VLOOKUP
Usage: The VLOOKUP function is used to find things in a table or a range by row. It can be combined with the IFS function to create more powerful lookups based on multiple conditions.
Example: You have a table with employee names, IDs, and their departments. You want to find the department of an employee based on their ID. Here’s how you can use IFS and VLOOKUP together:
=IFS(VLOOKUP(E2,A2:C10,3,FALSE)=”Sales”, “Sales Employee”, VLOOKUP(E2,A2:C10,3,FALSE)=”Marketing”, “Marketing Employee”)
This formula will return “Sales Employee” if the department of the employee with the ID in E2 is “Sales”, and “Marketing Employee” if the department is “Marketing”.
For more details on the IFS function, check out the official documentation at the Google Docs Editors Help Center.
