Using IFS Function in Google Sheets to Test Multiple Conditions

In Google Sheets, you can use the IFS function to test multiple conditions at once and then return the result based on it. The difference between the IF function and the IFS function is that in IFS function, you can test multiple conditions at once. As soon as the condition is found to be TRUE, its corresponding value is returned.

When to Use Excel IFS Function

IFS function can test multiple conditions and returns the value as soon as it finds the first condition that is fulfilled. This makes this function ideal for situations where you need to find the grade for students based on their score. Another situation where it can come in handy is when you have to calculate the commission based on the sales values (these will be covered in examples below). Let’s first have a look at the syntax of IFS function in Google Sheets.

Excel IFS Function – Syntax

=IFS(Condition1, Value1, [Condition2, Value2],…)
  • Condition1 – The first condition that is checked.
  • Value1 – The value to return if the first condition is TRUE.
  • [Condition2…] – These are optional arguments. You can specify additional conditions here. Note that every condition needs to be followed by the value to be returned for that condition.
  • [Value2….Value127] – These are optional arguments. Each value corresponds to its condition and would be returned if it’s condition is the first one to be TRUE.
Additional Notes:
  • IFS function would return the value of the first TRUE condition only. So you can have multiple conditions that are TRUE. However, only the value for the first one would be returned.
  • All the conditions in the IFS function must return either TRUE or FALSE. If it doesn’t, the formula will give an error.
  • If all the conditions in the IFS function return FALSE, the result of the formula would be the #N/A error. Since #N/A error is not very helpful in finding out what happened, you can use the last Condition as TRUE, and the value as FALSE or a descriptive text such as “No Match”.

Using Excel IFS Function – Examples

As I mentioned earlier, this function is best suited for situations where you need to check conditions in a sequence and return the value for the first condition that is met. This makes it ideal for situations where you have to grade students or find commissions for the sales team.

Example 1 – Calculating Commission Based on the Sales

Suppose you’re the head of the sales department and it’s time to give out commissions based on the sales done by each employee. Calculating Commission using IFS function in Google Sheets Below is the formula that will give you the commission value for each sales personnel:
Calculating Commission using IFS function in Google Sheets Formula

Example 2 – Finding Student’s Grade Based on the Score

Suppose you have the scores for students in an exam as shown below (column A) and the grading conditions in column C and D. Calculating Grades using IFS Formula in Google Sheets Below is the formula that will give you the grades for each student:
Calculating Grades using IFS function in Google Sheets
Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.

Popular Posts

3 thoughts on “Using IFS Function in Google Sheets to Test Multiple Conditions”

  1. So I’m trying to make a kind of if then statement almost? Heres teh command im trying to get.
    =IFS(K23>1,W30,K23<35,"35") But for some reason it just wont replace the number i need it to if its over 35

  2. guys i’ve spend 5 hours. I just can’t figure this out, so i seek help from you and god himself.
    I want to make an IF statement between theese numbers.
    7% $1,000.00
    8% $10,000.00
    9% $50,000.00
    9.50% $100,000.00

    how do I make if between 1000 and 9999 then show 7% or between 10000 and 49999 then show 8% etc.. etc…

    all my love

    • Hi, not sure if this is still useful or if it has already been replied since it’s been one year already.

      So the IFS function reads from left to right and returns the value of the first condition that has been verified as true, so if you do:

      *let’s say cell A1 has the value you want to check and cell B1 is the cell where you want the % to be displayed*

      =IFS(A1>=1000, A1=1000″ occupies the slot for the 1st condition, while “A1=1000”, if true it will return a boolean value “TRUE” (since it doesnt recognize “A1=1000,A1=1000,A1=10000,A1=1000,A1=10000,A1=50000,A1<=99999),9%)

      Note that while the IF function can return a value (the "" in the previous example) when none of the conditions are met, the IFS function cannot do the same. When none of the condition in the IFS function are met it will return "#N/A".

      Sorry por the long reply, I hope it is still helpful.

      Best Regards

Comments are closed.