IFS

The IFS function checks specified expressions and returns a value based on the first condition that evaluates to a boolean value of TRUE.

IFS(ifs-expression, ifs-true, ifs-expression…, ifs-true…)

ifs-expression: A logical expression. ifs-expression can contain anything as long as the expression can be evaluated as a boolean value. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE.

ifs-true: The value returned if ifs-expression is TRUE. ifs-true can contain any value. If ifs-true is omitted (there’s a comma, but no value) and ifs-expression evaluates to TRUE, IFS will return 0.

ifs-expression…: An optional logical expression to be evaluated if ifs-expression is FALSE. ifs-expression can contain anything as long as the expression can be evaluated as a boolean value. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE.

ifs-true…: The value returned if ifs-expression… is TRUE. ifs-true can contain any value. If ifs-true… is omitted (there’s a comma, but no value) and ifs-expression… evaluates to TRUE, IFS will return 0.

Notes

If all expressions are FALSE, an error is returned.
You can enter “TRUE” for the last ifs-expression… in your formula, followed by a default value for ifs-true…, to specify the result if all previous expressions (including ifs-expression and all instances of ifs-expression…) evaluate to FALSE.

Examples
=IFS(A2>91,”A”,A2>82,”B”,A2>73,”C”,A2>64,”D”,TRUE,”F”) returns the letter grade “A” for a number greater than 91, then returns a “B” for a number greater than 82 but less than 92, and so on for all values less than 65, which returns an “F”. Let A2 contain "A dog" Let A1 = COUNTMATCHES(A2, REGEX("\w+")) =IFS(A1 = 0, "No word", A1 = 1, "One word", A1 = 2, "Two words", A1 > 2, "Multiple words") returns "Two words".

Examples

=IFS(A2>91,”A”,A2>82,”B”,A2>73,”C”,A2>64,”D”,TRUE,”F”) returns the letter grade “A” for a number greater than 91, then returns a “B” for a number greater than 82 but less than 92, and so on for all values less than 65, which returns an “F”.

Let A2 contain "A dog"

Let A1 = COUNTMATCHES(A2, REGEX("\w+"))

=IFS(A1 = 0, "No word", A1 = 1, "One word", A1 = 2, "Two words", A1 > 2, "Multiple words") returns "Two words".

See alsoAND IF NOT OR

Helpful?

Formulas and Functions Help

IFS

Notes