IFS
TheIFS
formula evaluates multiple conditions and returns a corresponding value for the first condition that is true. It is commonly used as a more concise alternative to nested IF
statements.
- How to use
IFS
formula? - Examples of using
IFS
formula IFS
formula not working?- Similar formulas to
IFS
Usage
Use the IFS
formula with the syntax shown below, it has 2 required parameters:
- condition1, condition2, ... (required):
The conditions to evaluate. Each condition should be a logical expression that returns eitherTRUE
orFALSE
. - value1, value2, ... (required):
The values to return if the corresponding condition is true.
Examples
Here are a few example use cases that explain how to use theIFS
formula in Google Sheets.
Return a grade based on a score
Use IFS
to return a letter grade based on a numerical score. For example, if the score is greater than or equal to 90, return an A; if it is greater than or equal to 80, return a B; and so on.
Categorize expenses
Use IFS
to categorize expenses based on their amount. For example, if the expense is less than $50, categorize it as a minor expense; if it is greater than or equal to $50 but less than $100, categorize it as a moderate expense; and so on.
Calculate shipping costs
Use IFS
to calculate shipping costs based on the weight of an item and the destination. For example, if the weight is less than 1 pound and the destination is within the United States, the shipping cost is $5; if the weight is between 1 and 5 pounds and the destination is within the United States, the shipping cost is $10; and so on.
Common Mistakes
IFS
not working? Here are some common mistakes people make when using the IFS
Google Sheets Formula:
Incorrect number of arguments
One of the most common mistakes is to provide an incorrect number of arguments to the IFS formula. Make sure that you provide at least one condition-value pair and that each condition is followed by a corresponding value. Also, note that the maximum number of conditions that can be evaluated is 127.
Condition not met
Another common mistake is to provide conditions that are not met. If none of the conditions are met, the formula will return an error. Double-check your conditions to make sure they are correct and that they cover all possible scenarios.
Incorrect syntax
Make sure that you provide the correct syntax for the IFS formula. The syntax requires that you provide at least one condition-value pair, and that each condition is followed by a corresponding value. Also, make sure that you separate multiple condition-value pairs with a comma.
Nested IFS functions
Avoid using nested IFS functions as they can quickly become complex and difficult to manage. Instead, consider using other logical functions such as IF, AND or OR.
Wrong data type
Make sure that the data type of the conditions and values you provide is correct. For example, the condition must be a logical expression that evaluates to either true or false, and the value can be any data type.
Related Formulas
The following functions are similar to IFS
or are often used with it in a formula:
-
IF
The
IF
formula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions. -
SWITCH
The
SWITCH
formula is a logical function in Google Sheets that allows you to evaluate an expression against a list of cases and corresponding values. If the expression matches a case, the formula returns the corresponding value. If there is no match and a default case is specified, the formula returns the value for the default case. This function is commonly used for conditional formatting, data validation, and other tasks that require evaluating multiple conditions. -
AND
The
AND
function in Google Sheets is a logical function that returns TRUE if all of the logical expressions in the argument are TRUE, and FALSE if any of the logical expressions are FALSE. It can be used to test multiple conditions and evaluate whether they are all true or not. -
OR
The
OR
formula is a logical formula that returns TRUE if at least one of the provided logical expressions is TRUE. It returns FALSE if all logical expressions are FALSE. This formula is commonly used in conjunction with other logical formulas, such asAND
, to build more complex logical statements.
Learn More
You can learn more about the IFS
Google Sheets function on Google Support.