IFS comes in the category of logical functions. IFS function can be used for logical comparison of values and to get the first result when the comparison is TRUE and second result when the comparison is FALSE. IFS function can do multiple tests in a single run and is a very good alternative of nested IF function. It is evaluated by putting various logical operators like “=”,”>”,”<” etc. in the formula .IFS function is also used in combination with other functions like AND , OR , COUNT etc.
=IFS(Test1, Value1, Test2, Value2)
- Test1- It is the first test to be performed.
- Value1- It is the value to be returned if Test1 is TRUE.
- Test2/Value2 – These are the next pairs of test to be performed and value if True and so on.
- IFS calculates multiple tests in a single run and is a very good alternative to the nested IF function.
- If any of the test returns value other than TRUE OR FALSE, #VALUE! error is displayed.
- If no TRUE value is returned in any one of all the tests than #N/A error is displayed.
- IFS function is case insensitive when we compare the text.
- If any of the arguments to IFS have been used as arrays, the IFS function can measure each part of the array.
- To count things with conditions, use the COUNTIF or the COUNTIFS functions.
- To add things with condition, use the SUMIFS or the SUMIF functions.
- IFS function can run 127 condition in a single go.
- IFS function is only available is Excel 2016 and newer versions.
When using IFS Function, the following logical operators can be used
|>=||Greater than or equal to|
|<=||Less than or equal to|
|<>||Not equal to|
In the following example, multiple conditions are run using IFS to identify the type of products in Column A and Results are entered in Column C.