*From this short tutorial you'll learn about the new IFS function and see how it simplifies writing nested IF in Excel. You'll also find its syntax and a couple of use cases with examples. *

Nested IF in Excel is commonly used when you want to evaluate situations that have more than two possible outcomes. A command created by nested IF would resemble "IF(IF(IF()))". However this old method can be challenging and time consuming at times.

The Excel team has recently introduced the IFS function that is likely to become your new favorite one. Excel IFS function is available only in Excel 2016 that is part of Office 365 subscriptions, Excel Online and Mobile, Excel for Android tablets and phones.

The IFS function in Excel shows whether one or more conditions are observed and returns a value that meets the first TRUE condition. IFS is an alternative of Excel multiple IF statements and it is much easier to read in case of several conditions.

Here's how the function looks like:

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]…)

It has 2 required and 2 optional arguments.

**logical_test1**is the required argument. It's the condition that evaluates to TRUE or FALSE.**value_if_true1**is the second required argument that shows the result to be returned if logical_test1 evaluates to TRUE. It can be empty, if necessary.**logical_test2…logical_test127**is an optional condition that evaluates to TRUE or FALSE.**value_if_true2…value_if_true127**is an optional argument for the result to be returned if logical_testN evaluates to TRUE. Each value_if_trueN relates to a condition logical_testN. It can also be empty.

Excel IFS lets you evaluate up to 127 different conditions. If a logical_test argument doesn't have certain value_if_true, the function displays the message "You've entered too few arguments for this function". If a logical_test argument is evaluated and corresponds to a value other than TRUE or FALSE, IFS in Excel returns the #VALUE! error. With no TRUE conditions found, it shows #N/A.

The benefit of using the new Excel IFS is that you can enter a series of conditions in a single function. Each condition is followed by the result that will be used if the condition is true making it straightforward to write and read the formula.

Let's say you want to get the discount according to the number of licenses the user already has. Using the IFS function, it will be something like this:

`=IFS(B2>50, 40, B2>40, 35, B2>30, 30, B2>20, 20, B2>10, 15, B2>5, 5, TRUE, 0)`

Here's how it looks with nested IF in Excel:

`=IF(B2>50, 40, IF(B2>40, 35, IF(B2>30, 30, IF(B2>20, 20, IF(B2>10, 15, IF(B2>5, 5, 0))))))`

The IFS function below is easier to write and update than its Excel multiple IF equivalent.

`=IFS(A2>=1024 * 1024 * 1024, TEXT(A2/(1024 * 1024 * 1024), "0.0") & " GB", A2>=1024 * 1024, TEXT(A2/(1024 * 1024), "0.0") & " Mb", A2>=1024, TEXT(A2/1024, "0.0") & " Kb", TRUE, TEXT(A2, "0") & " bytes")`

`=IF(A2>=1024 * 1024 * 1024, TEXT(A2/(1024 * 1024 * 1024), "0.0") & " GB", IF(A2>=1024 * 1024, TEXT(A2/(1024 * 1024), "0.0") & " Mb", IF(A2>=1024, TEXT(A2/1024, "0.0") & " Kb", TEXT(A2, "0") & " bytes")))`

If you need more examples of using nested Ifs, have a look at

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 13 Responses to "Use the new Excel IFS function instead of nested IF"

The hyperlink above for the SWITCH tutorial is broken (it has the sitename twice). Correct URL for the interim is https://www.ablebits.com/office-addins-blog/2016/05/02/excel-switch-function/

self-reply: same issue affects all three URL in the "You may also be interested" section.

Thank you so much for pointing this out to us, Mike! Fixed :)

I need excel function to calculate and update old selling price of same product number located in different rows in same column

example:

Product#1001-099

Old Price = $20.00

New Price should be increased by 5%

Product is in Column A

Selling Price is Column D

How do I increase and replace the old Selling Price

for this product that is in Column A,

Row 8, Row 101, Row228?

Thank you for your assistance

Hi Faye,

You need to add the additional column in your table and use the following formula to increase the price by 5%:

=D1*1.05

Then please copy the added column and use the Paste Special - Values option to replace the values in Column D.

=IF(AND(C3:BA3=0), "DISTRICT LEADER",IF(AND(C3>=15, D3>=5),"DIVISONAL LEADER",IF(AND(C3>=15, D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,C3>=15,D3>5, I3>=45),"REGIONAL LEADER",IF(AND(C3>=15,D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,I3>=45,K3>=3,L3>=3,M3>=5,N3>=15,O3>=45,P3>=45,Q3>=75),"SENIOR REGIONAL LEADER ",IF(OR(C3>=15,D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,I3>=45,K3>=3,L3>=3,M3>=5,N3>=15,O3>=45,P3>=45,Q3>=75,S3>=4,T3>=3,U3>=3,V3>=5,W3>=15,X3>=60,Y3>=45,Z3>=45,AA3>=75), "REGIONAL MANAGER", IF(AND(C3>=15,D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,I3>=45,K3>=3,L3>=3,M3>=5,N3>=15,O3>=45,P3>=45,Q3>=75,S3>=4,T3>=3,U3>=3,V3>=5,W3>=15,X3>=60,Y3>=45,Z3>=45,AA3>=75,AC3>=5,AD3>=4,AE3>=3,AF3>=3,AG3>=5,AH3>=15,AI3>=75,AJ3>=60,AK3=45,AL3>=45,AM3>=75), "SNR. REG. MANAGER", IF(AND(C3>=15,D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,I3>=45,K3>=3,L3>=3,M3>=5,N3>=15,O3>=45,P3>=45,Q3>=75,S3>=4,T3>=3,U3>=3,V3>=5,W3>=15,X3>=60,Y3>=45,Z3>=45,AA3>=75,AC3>=5,AD3>=4,AE3>=3,AF3>=3,AG3>=5,AH3>=15,AI3>=75,AJ3>=60,AK3=45,AL3>=45,AM3>=75,AO3>=6,AP3>=4,AQ3>=3,AR3>=3,AS3>=5,AT3>=15,AU3>=90,AV3>=75,AW3>=60,AX3>=45,AY3>=45,AZ3>=45,BA3>=75),"RVP", "SEN. REG. MANAGER")))))))

hi

why in my excel(2010 version) "IFS" function does not work??

what should I do??

Google pwrIFS as an option.

with nested IF(), latter redundant calculations are ignored (not calculated)

=IF(1=1,"Answer","this bit could be processor heavy but is ignored")

but using the 'evaluate formula' it would appear that IFS() works out all the bits of the formula, even if the first condition is met and therefore is redundant

=IFS(1=1,"answer", other condition IS verified, redundant terms all calculated)

Is this the case?

essentially, IFS does indeed look simpler to untangle, BUT is it actually getting excel to do a less efficient amount of calculating?

Hello, Mike,

We haven’t investigated the efficiency of the IF() and IFS() functions in all configurations.

Most likely, they work in the same way. For example, if you create two formulas like the ones below:

=IF(1=1,"1",IF("=1","2"))

=IFS(1=1,"1", "=1", "2")

They return the same result equal to «1».

But following your hypothesis, the =IFS(1=1,"1", "=1", "2") formula should have returned a #VALUE! error as the second condition in the formulas is wrong.

Mike you are correct. It appears as though IFS is horribly inefficient compared to traditional nested IFS statements. Much easier to write but you end up sitting around longer waiting on calculations to finish.

Hi everybody...

I have problem related, I think, to using IF function or IFS.

I have a Pivot filter including 3 options, means I can select 1 or All or Multiple product categories. I am using SUMIFS to extract the data from Excel table, and filter using Pivot filter. all working fine until I select 2 product categories, I eliminate the problem when selecting (ALL), but could not do that when I select 2 product categories, the result was all zero values. here is my function:

=SUMIFS(SalesData[revenue];SalesData[year];I$4;SalesData[region];$H5;SalesData[Category];IF($I$2="(All)";"*";$I$2))

my attempt was nested IF:

IF($I$2="(All)";"*";IF($I$2="(Multiple Items)";$I$2))

but it dose not work, any help...

Every 2 weeks a payment is made and entered, the beginning amount of payment are decreased by 1 every second Thursday. During the off weeks an extra payment can be made and is entered in a separate Col. Col 3. When the extra payment is made i would like the reflection of that payment shown in Col 2 by subtracting from the number of payments remaining and not effecting the bi-weekly payment / remaining payments.

Payments remaining = 110

Col. 1 has a payment of $200.00

Col. 2 has 109 (number of payments remaining)

with an IF statement,

-if(col. 1>0,payments remaining-1,"")

Note that each payment made the IF statement changes from Payments remaining-1 to Col. 2 (previous cell number) -1 so it would be

-if(col. 1>0,Previous cell-1,"") Previous cell being 109

In addition to this there is another Col, Col. 3 for EXTRA payments where, when entered on a row with no other payments entered, i would like THAT payment reflected in the payments remaining total.

If you want the spreadsheet let me know .... can email it to you

Thanks

Cat