Skip to main content

Excel IFS function

As we all known, we can use the Nested IF function to test whether multiple conditions are met. But here, the IFS function is much easier to use than the Nested IF function. IFS function is used to test multiple conditions and return a value corresponding to the first TRUE result, if none of the supplied conditions evaluate to TRUE, the function returns the #N/A error.

doc ifs function 1


 Syntax:

The syntax for the IFS function in Excel is:

=IFS( logical_test1, value_if_true1, [logical_test2, value_if_true2],... )

 Arguments:

  • logical_test1: Required. The condition that evaluates to TRUE or FALSE.
  • value_if_true1: Required. Returns the result if logical_test1 is TRUE. It can be empty.
  • logical_test2: Optional. The second condition that evaluates to TRUE or FALSE.
  • value_if_true2: Optional. Returns the second result if logical_test2 is TRUE. It can be empty.

Notes:

  • 1. This IFS function lets you enter up to 127 conditions. And it is applied to Excel 2019, Office 365. All versions earlier than Excel 2019 don’t support this function.
  • 2. If no logical tests evaluate to TRUE, it will return the #N/A error.
  • 3. A #VALUE error value occurs if the supplied logical_test returns any value other than TRUE or FALSE.

 Return:

To test multiple conditions, and return corresponding value with first TRUE result.


 Examples:

Example 1: Use IFS functions for identifying the grade based on score

To assign the grade for each student based on their scores as following screenshot shown:

doc ifs function 2

Please apply the below formula, so the grade will be categorized as this: 0-60: grade F; 60-70: grade D; 70-80: grade C; 80-90: grade B; greater or equal to 90: grade A.

=IFS(B2<60,"F",B2<70,"D",B2<80,"C",B2<90,"B",B2>=90,"A")

doc ifs function 3

Note: You can also use the cell references to replace the static numbers as this:

=IFS(B2<$E$3,$F$2,B2<$E$4,$F$3,B2<$E$5,$F$4,B2<$E$6,$F$5,B2>=$E$6,$F$6)

doc ifs function 4


Example 2: Use IFS functions for calculating commission based on sales

The IFS function can help you to calculate commission amounts based on varying commission rates and cumulative sale totals for each month.

Supposing, you have a table with all salesmen’s total sales and commission rates as following screenshot shown, how could you calculate the commission based on the various commission rate in Excel?

doc ifs function 5

The following formula can give the commission value for each sales personnel, please use this:

=IFS(B2<$E$3,$F$2,B2<$E$4,$F$3,B2<$E$5,$F$4,B2>$E$5,$F$5)*B2

which means:

  • if the sales total is 0-40000: commission= sales * 0;
  • if the sales total is 40000-80000: commission= sales * 2%;
  • if the sales total is 80000-100000: commission= sales * 3.5%;
  • if the sales total is greater or equal to 100000: commission= sales * 7%;

doc ifs function 6


Tips: The IFS function will get #N/A error, if none of the conditions evaluate to TRUE. If you want to replace the #N/A error by another meaningful value, you should add an ELSE condition at the end of the IFS function.

Normally, use the IFS function:

=IFS(B2="Apple","Fruit", B2="Orange","Fruit", B2="Potato","Veg",B2="Steak","Meat", B2="Chicken","Meat")

doc ifs function 7

Use the IFS function with ELSE condition:

=IFS(B2="Apple","Fruit", B2="Orange","Fruit", B2="Potato","Veg",B2="Steak","Meat", B2="Chicken","Meat", "TRUE","Others")

Note: In the above formula, the addition of the final condition, "TRUE","Others" will return the value "Others" when none of the conditions in the IFS function evaluate to TRUE.

doc ifs function 8


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Why isn't this function in my Office 365 Excel version Dutch? Or am I missing something?
This comment was minimized by the moderator on the site
the IFS function 😃
This comment was minimized by the moderator on the site
need help Score Grade
90-100 A
80-89 B
70-79 C
60-69 D
0-59 F
This comment was minimized by the moderator on the site
Hi, cgutierrez,Please apply the below formula:=IFS(A1<60,"F",A1<70,"D",A1<80,"C",A1<90,"B",A1>=90,"A")
Please try, hope it can help you!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations