Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS.

WARNING: Not all versions of Excel 2016 have this new function. If you have the Office 365 subscription then you should have it. Otherwise you may have to wait until the next full Excel version release, next year.

The simplification is that instead of handling the TRUE and FALSE actions and having to nest your IF functions, you only have to handle the TRUE action and there is no need to nest IF functions.

If you are not familiar with the IF function it has the following syntax.

IF(Logical_Test,Value_if_True,Value_if_False)

The Logical_Test is a formula that must return TRUE or FALSE.

The Value_if_True and Value_if_False arguments can be text, values, or other calculations. If you use another IF function it’s called nesting the IF functions.

Example

Let’s have a look at an example.

We have a drop-down list in cell F2 that lists states. We have three state columns on the left-hand side. We need to create a formula that will add up the correct column based on the selection in cell F2. There are a number of ways to calculate this but we will use a straightforward IF function. We will then compare how we can calculate this with the IFS function.

Shorter formulas: see discussion at the bottom of the post on how to shorten some IF and IFS functions.

An IF function that will work correctly is

=IF(F2=A1,SUM(A2:A8),IF(F2=B1,SUM(B2:B8),IF(F2=C1,SUM(C2:C8),0)))

This tests cell F2 again each state heading cell and does a SUM calculation if it matches. If none of the three states are found then zero is returned.

As you can see when you nest your IF functions it quickly becomes difficult to follow the calculation. It is usually recommended to only nest to three or four levels in an IF function. More than that and you should be rethinking the calculation and possibly using a table or helper cells to handle the conditions.

The syntax for the IFS function is

IFS(Logical_Test_1, Value_if True_1, Logical_Test_2, Value_if_True 2,…)

You can have multiple pairs of Logical_Tests and Value_if_True arguments.

The alternative formula using the IFS function is simpler. See example below.

=IFS(F2=A1,SUM(A2:A8),F2=B1,SUM(B2:B8),F2=C1,SUM(C2:C8),TRUE,0)

As you can see the logical tests are all the same as the IF function, but we only need to provide a true action for each logical test.

The important technique to use when using IFS function is to provide a default “none of the above” option at the end of the function. Simply enter the word TRUE for the last logical test and then provide the default action. If you leave out the default logical test then you may end up with an #N/A error if none of the other conditions are met.

Alternative Shorter Formulas

One area that you can reduce formula length is by removing multiple functions from IF functions. For example in both examples above we had a number of SUM functions listed within the IF and IFS functions. Both the IF function and the IFS function can return a range. This means that in the examples above you can put the SUM function at the front of the calculation and have the IF or IFS function return a range for the SUM function to use.

The re-written, shorter functions are shown below.

=SUM(IF(F2=A1,A2:A8,IF(F2=B1,B2:B8,IF(F2=C1,C2:C8,0))))

=SUM(IFS(F2=A1,A2:A8,F2=B1,B2:B8,F2=C1,C2:C8,TRUE,0))