Skip to Content

How to use Excel IFS Function (With Examples)

The Excel IFS function allows you to perform multiple logical tests and returns a value corresponding to the first condition that evaluates to TRUE. It is the best alternative to nested IF statements.

Let’s understand the function through an example. Suppose that you are a teacher. Your task is to grade each student as:

  • Diamond: Scored 90+ marks
  • Gold: Scored 60+ marks
  • Bronze: Scored 40+ Marks
  • Failed: Score is below 40

Here is the example data,

Our formula would be:

=IFS(B2>=90, "Diamond", B2>=60, "Gold", B2>=40, "Bronze", B2<40, "Failed")

Where,

  • B2>=90 is the first condition to check if the student’s score is greater than or equal to 90
  • Diamond is the desired output if the student’s score is 90+
  • B2>=60 checks if the student’s score is greater than or equal to 60
  • Gold is the desired output if the second condition is true
  • B2>=40 checks if the student’s score is greater than or equal to 40
  • Bronze is the desired output if the third condition is true
  • B2<40 condition checks if the student failed the given exam

The output is displayed in the following image.

Syntax

The IFS is a simple logical function in Excel. Here’s the syntax of the function.

= IFS(logical_test1, Value1, [logical_test2, Value2] …, [logical_test127, Value127])

Where,

  • logical_test1, logical_test2, logical_test127… are the arguments where you can define the conditions
  • Value1, Value2, Value127… are the arguments where you can define values to be displayed if the corresponding condition is met

You can use the following comparison operators to define conditions or criteria.

  • “=” – Equal To
  • “<“- Less Than
  • “<=” – Less Than or Equal To
  • “>” – Greater Than
  • “>=” – Greater Than or Equal To
  • “<>” – Not Equal To

Important Notes:

  • The function accepts a maximum of 127 conditions
  • It is not case-sensitive
  • All text values must be enclosed within double quotes
  • The function does not support wildcards

To learn the difference between the IF and IFS functions in Excel, click here.

How to use the IFS Function in Excel

Download the example spreadsheet used to demonstrate the IF function in the upcoming section. Practice is the key to success!

Download Now

Example 1: IFS Function Basics

Suppose that you own an online store. You are preparing a monthly report. The task is to find how well different products are selling. We have the previous month’s data, which includes the total quantities sold per item.

Refer to the following image,

Let us categorise the above products as:

  • Best Seller: More than 500 quantities are sold
  • Average Performer: More than 300 quantities are sold
  • Slow Mover: More than 100 quantities are sold
  • Dead Inventory: Less than 100 quantities are sold

Here are the steps,

  • Select the cell C2
  • Type =IFS
  • Select the first option from the popup
  • Specify the first condition as B2>=500
  • Type , on your keyboard
  • Specify the value “Best Seller” to be displayed if the first condition is met
    (Make sure to use the double quotation marks as this is a text value)
  • Type , on your keyboard
  • Now, put the second condition B2>=300
  • Type ,
  • Specify the value “Average Performer” to be displayed if the first condition is FALSE and the second is TRUE
    (Make sure to use the double quotation marks as this is a text value)
  • Type ,
  • The third condition will be B2>=100
  • Type ,
  • Specify the value “Slow Mover” to be displayed if the first and second conditions are FALSE, but the third condition is TRUE
    (Make sure to use the double quotation marks as this is a text value)
  • Type ,
  • Put the fourth condition as B2<100
  • Type ,
  • Specify the value “Dead Inventory” to be displayed if all three conditions are FALSE and the fourth is TRUE
    (Make sure to use the double quotation marks as this is a text value)
  • Complete the bracket )
  • Press the Enter key

Our final formula is as follows,

=IFS(
    B2>=500, "Best Seller",
    B2>=300, "Average Performer",
    B2>=100, "Slow Mover",
    B2<100, "Dead Inventory"
)

Where,

  • B2>=500 is the first condition. It checks if the total quantities sold are greater than or equal to 500.
  • B2>=300 is the second condition. It checks if the total quantities sold are greater than or equal to 300.
  • B2>=100 is the third condition. It checks if the total quantities sold are greater than or equal to 100.
  • B2<100 is the fourth condition. It checks if less than 100 quantities of the given product are sold.

The results are displayed in the following image,

Example 2: Inserting the formulas in place of the value argument

Consider the following data. We have employee names along with their salary and performance score. We plan to reward employees with remarkable performance.

We will assign the following rating to each employee based on their scores:

  • Excellent: Scored 90+
  • Good: Scored 75+
  • Average: Scored 60+
  • Needs Improvement: Score is below 60

Then, we will offer a portion of their monthly salary as a bonus.

  • 80% of his/her salary if the performance rating is Excellent
  • 50% of his/her salary if the performance rating is Good
  • 20% of his/her salary if the performance rating is Average
  • No bonus for the employees who got Needs Improvement as a performance rating

This task may sound a bit complex. But thanks to the IFS function in Excel, your life is easy now.

  • Select the cell C2
  • Type =IFS
  • Double-click on the IFS command from the popup
  • Specify the first condition as B2>=90
  • Type , to move to the next argument of the function
  • Put C2*0.8 in the place of value1 argument
    (It is the desired value to be returned if the first condition is TRUE)
  • Type ,
  • Specify the second condition as B2>=75
  • Type ,
  • Put C2*0.5 in the place of the value2 argument
    (It is the desired value to be returned if the first condition is FALSE and second condition is TRUE)
  • Type ,
  • Specify the third condition as B2>=60
  • Type ,
  • Put C2*0.2 in the place of value3 argument
    (It is the value to be returned if the first and second condition are FALSE, but the third condition is TRUE)
  • Type ,
  • Specify the fourth condition as B2<60
  • Type ,
  • Put “No Bonus” in the place of value4 argument
    (It is the value to be returned if the first, second, and third conditions are FALSE, but the fourth condition is TRUE)
  • Complete the bracket using )
  • Hit the Enter key

Our formula is as follows:

=IFS(
      B2>=90, C2*0.8,
      B2>=75, C2*0.5,
      B2>=60, C2*0.2,
      B2<60, "No Bonus"
)

Where,

  • B2>=90 is the first condition. It identifies the employees whose performance was Excellent in the given year.
  • C2*0.8 is the formula to calculate 80% of the salary. It is the desired output only when the first condition is TRUE.
  • B2>=75 is the second condition. It identifies the employees whose performance was Good in the given year.
  • C2*0.5 is the formula to calculate 50% of the salary. It is the desired output only when the first condition is FALSE and the second condition is TRUE.
  • B2>=60 is the third condition. It identifies the employees whose performance was Average in the given year.
  • C2*0.2 is the formula to calculate 20% of the salary. It is the desired output only when the first and second conditions are FALSE, but the third condition is TRUE.
  • B2<60 is the second condition. It identifies the employees whose performance needs improvement.
  • No Bonus is the desired output when the first, second, and third conditions are FALSE, but the fourth condition is TRUE.

The results are displayed in the following image,

Difference Between the IF and IFS Function in Excel

They are both prebuilt logical functions in Excel.

  • The IF function is used to test a single condition and return one value if the condition is true or another value if the condition is false.
  • The IFS function lets you test multiple conditions simultaneously and return the value corresponding to the first TRUE condition.

To understand the IF function, consider that you need to identify the number of sales reps who could get more than a $10K incentive in the given month. We are assuming that the incentive is mentioned in column C.

=IF(C1>=10000,”Yes”,”No”)

Where,

  • C1>=10000 is the logical test to check if the value in the cell C1 is greater than or equal to 10,000
  • Yes is the desired output if the condition is met
  • No is the desired output if the condition is not met

The function is simple to use. It has the following syntax,

=IF(logical_test,[value_if_true],[value_if_false])

Where,

  • logical_test is the first argument, where you can define the condition to be tested
  • value_if_true argument lets you define the desired output to be displayed if the condition is satisfied
  • value_if_false argument lets you define the desired output to be displayed if the condition is not satisfied

Click here to read our in-depth guide: How to use Excel IF Function (With Examples)

Takeaway

Both IF and IFS are powerful logical functions in Excel. You can combine them with other functions like AND and OR to create complex formulas.

The IFS function lets you deal with complex scenarios where multiple conditions need to be tested. Gone are the days when you had to use the nested IF function to solve such problems (including multiple conditions). 

Please comment below if you are stuck somewhere or having any particular error while using the OR function. I will answer your questions as soon as possible.

Additional Resources:

  • Learn All Excel Logical Functions (With Examples)
  • IF Function in Excel
  • AND Function in Excel
  • OR Function in Excel
  • NOT Function in Excel
  • XOR Function in Excel
  • Ultimate Guide to Comparison Operators in Excel
  • Excel Shortcuts List

Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.