Use the new Excel IFS function instead of nested IF

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 Excel IFS function - description and syntax

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 IFS function vs. nested IF in Excel with use cases

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)
The example of writing IFS function in Excel

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 example of writing nested IF function in Excel

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")
Conversion using IFS

=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")))
Conversion using nested IF

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

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

  1. Mikey says:

    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/

  2. Faye says:

    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

  3. 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.

  4. Fred says:

    =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")))))))

  5. Mahmood says:

    hi
    why in my excel(2010 version) "IFS" function does not work??
    what should I do??

  6. Mike De Butts says:

    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.

    • Brady says:

      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.

  7. Abdu says:

    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...

  8. Cat M Bourque says:

    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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard