How to use IFS Function in Google Sheets (With Examples)

how to use IFS Function in Google Sheets 23
Key Takeaways: How to use IFS Function in Google Sheets

To use the IFS Function in Google Sheets

  1. Prepare your sample data with students’ names and marks.
  2. Create a table for grading criteria with conditions and corresponding grades.
  3. In a cell, write the IFS function.
  4. Begin by checking the first condition.
  5. For example, =IFS(B4 < $E$5, $F$5), where B4 is the student’s mark, E5 is the lower limit, and F5 is the corresponding grade.
  6. Continue like this, then press Enter.

Hi. In this article, we will learn about how to use IFS function in google sheets.

IFS function is nothing but a combination of IF, which means that if we can use more than 1 condition in the IF then it will become IFS, which means the plural of If. Now you can guess what else this function can do. This can be referred to as Nested If else in the programming context. In almost every programming language we have an IF statement, and most probably we use IF else nested to examine multiple conditions together. Also, we can refer to IFS with the “Switch” statement in the context of general-purpose programming languages like Python and JavaScript.

Here IFS makes it very easy and simple to work on multiple conditions. Using if we can examine one condition such as do this if the value is greater than 100. but, in IFS we can examine multiple conditions together such as to that if the value is greater than this, this, this, and this. Now “this” is the values here that will be matched separately by the IFS and then a result will be brought.

Use Cases of IFS Function in Google Sheets

We many times need to work with IF else, and for that, we match some conditions with the values, we most times need to check the value with multiple conditions, so IFS is the solution for this. We can match multiple conditions (as many as we want) using IFS. There are many use cases (we will see later in this tutorial) that are required to perform multiple IF on that, but instead of using multiple If conditions we can easily use IFS to check more than one condition with only one formula and we can dynamic results using IFS.

Let’s move to practical learning in the below sections. We will learn some use cases to understand the need for the IFS function.

How to use IFS Function in Google Sheets

Here, we will learn how to learn IFS using some practical examples and real-life applications of IFS where we need to check more than one condition to bring out a single value.

syntax

=IFS(Condition1, Value1, [Condition2, Value2],…)

Here, condition 1 is any condition we want to check, then the value 1 after the comma, similarly after this we can continue the same syntax condition2, value2, and so on.

How to use IFS Function in Google Sheets – Calculate Students’ Grade

In this section, we will learn how to use the IFS function in google sheets to calculate the grade of the students based on their marks.

For this example, I have a dataset with students’ names and marks, and a small table of grading criteria where the criteria are written.

I will use the IFS function here to check all the conditions and where the marks matched, the adjacent grade will be assigned to the student. It’s pretty much like a VLOOKUP function.

Step 1

Sample data

how to use IFS Function in Google Sheets 1

Step 2

Grading Criteria data

how to use IFS Function in Google Sheets 2

Step 3

Start writing the IFS function
how to use IFS Function in Google Sheets 3

Step 4

Pass the condition to check let’s say the condition is (A2<E3, F4)

how to use IFS Function in Google Sheets 4

Step 5

Now following a similar pattern, you can pass all the conditions by adding commas between them

how to use IFS Function in Google Sheets 5

Step 6

The final formula will be:

=IFS(B4<$E$5,$F$5,B4<$E$6,$F$6,B4<$E$7,$F$7,B4<$E$8,$F$8,B4<$E$9,$F$9,B4<$E$10,$F$10,B4<E$11,$F$11,B4<$E$12,$F$12)

how to use IFS Function in Google Sheets 6

Step 7

Hit enter and drag down the formula to copy

how to use IFS Function in Google Sheets 7

how to use IFS Function in Google Sheets 8

how to use IFS Function in Google Sheets 9

You have got the appropriate grades of the students based on their marks.

This is how to use IFS Function in google sheets to calculate the Grade based on students’ marks.

How to use IFS Function in Google Sheets – Calculate Employees’ Incentives

In this section, we will learn how to use the IFS function in google sheets to calculate employees’ incentive amount based on their number of off days in the previous project duration. We will give good incentives if the off days are low, and no incentive if the off days are more than a certain number.

For this example, I have a sample dataset and also a table of incentive criteria.

Let’s see how it’s done.

Step 1

Sample data

how to use IFS Function in Google Sheets 10

Step 2

Incentive criteria table

how to use IFS Function in Google Sheets 11

Step 3

Write the IFS function

how to use IFS Function in Google Sheets 12

Step 4

Pass the conditions for every criterion in the incentive criteria table.

how to use IFS Function in Google Sheets 13

(Here we will use the equal to the operator instead of less than the operator, because the number of days is fixed from 0 to 7 there is nothing in between so we will go for exact matches)

Step 5

The final formula will be:

=IFS(J4=$M$5,$N$5,J4=$M$6,$N$6,J4=$M$7,$N$7,J4=$M$8,$N$8,J4=$M$9,$N$9,J4=$M$10,$N$10,J4=$M$11,$N$11,J4=$M$12,$N$12)

how to use IFS Function in Google Sheets 14

Step 6

Press Enter key and you’re done.

how to use IFS Function in Google Sheets 15

how to use IFS Function in Google Sheets 16

This is how to use the IFS function in google sheets to calculate the incentive amount for each employee based on their number of days off.

How to use IFS Function in Google Sheets – Calculate Delivery Charges

In this section, we have another use case. We will learn how to use the IFS function in google sheets to calculate the delivery charges for the customers based on their total shopping amount. We have to sample data and also a table called Delivery Charges Criteria. We will use the same approach to solve this and find the Delivery charges value by checking the shopping bill amount in the criteria table.

Step 1

Sample data

how to use IFS Function in Google Sheets 17

Step 2

Delivery charges criteria table

how to use IFS Function in Google Sheets 18

Step 3

Write the IFS formula

how to use IFS Function in Google Sheets 19

Step 4

Pass the conditions and compare the shopping bill with each value in the criteria table

how to use IFS Function in Google Sheets 20

how to use IFS Function in Google Sheets 21

(Here we will use the greater than the operator because we do not have exact values so we will compare with the greater than the operator)

Step 5

The final formula will be:

=IFS(S4>$V$8,$W$8,S4>$V$7,$W$7,S4>$V$6,$W$6,S4>$V$5,$W$5)

how to use IFS Function in Google Sheets 22

Step 6

Press Enter key, and you’re done.

how to use IFS Function in Google Sheets 23

This is how to use the IFS function in google sheets to calculate the delivery charges for each customer based on their total spending.

Comparison: IF vs IFS function in Google Sheets

  • I can check only one condition and return the result based on the value.
  • IF returns a single value based on the condition either true or false.
  • IFS can check as many conditions as you want
  • IFS requires to pass three arguments for each condition
  • IFS cannot have custom results if the condition is false, although you can print a custom message only when the condition is true.

Download/Copy Google Sheets Practice Workbook

Notes

  • IFS function does not have a false statement, if the condition is false the function will check for other conditions.
  • We have to determine the condition when the value is matched, we have to define the true result for every item although its true or not
  • Remember to use $ Notation with the condition and values after condition such as B4=$E$5,$F$5, because we want B4 to be changed as we copy the formula to other cells, and we want to keep E5, and F5, locked when the formula is copied.

Frequently Asked Questions

How to use the IFS function?

IFS function can be used just like IF in google sheets, we have a simple syntax of IFS. IFS has three arguments to make one condition, all are mandatory arguments. The function requires all the arguments in pairs after the 0th position. You cannot just define the condition and nothing if the condition is true. IFS can check multiple conditions at once unlike IF, which can only check one condition at once.

Why should we use IFS Function?

whenever we need to check multiple conditions, we should use IFS, for example, check if the condition is this do this, else if the condition is this, do this, else if the condition is this, then do this, and else do this. This kind of IF Else nested is very popular in programming and is pretty useful you will see many real-world examples where we need to deal with such scenarios. (Three real-world applications are discussed in this tutorial)

Difference between IF and IFS?

I can check a single condition in one iteration, and if you have 7 conditions to check then you need to use the IF condition 7 times. While if you have 100 conditions to check, then you need one IFS function that can check all 100 conditions in a single iteration, it will check all 100 conditions, which means that a value will be compared to all 100 possible conditions and where it becomes true or matched the function will assign the value and move to next value to match the next value with all 100 conditions.

Note: You can sometimes face time-complexity when using IFS for so many conditions.

What Are the Similarities and Differences Between SWITCH and IFS Functions in Google Sheets?

The google sheets switch function and the google sheets ifs function serve similar purposes in manipulating data within a Google Sheets document. However, they have distinct characteristics. The SWITCH function allows users to evaluate multiple conditions and return a value based on the first condition that is met. On the other hand, the IFS function evaluates multiple conditions and returns a value based on the condition that is true. These functions provide efficient ways to handle data analysis and decision-making in Google Sheets.

Why use IFS against VLOOKUPS?

There is nothing to using IFS instead of VLOOKUPS, instead, it’s all about the functions themselves. VLOOKUP is a more advanced function than IFS, but VLOOKUP can be a little difficult to understand if compared with IFS. You can use IFS or VLOOKUPS whatever you want to use. We have not compared these functions. Both are good, and both can solve similar problems. Although the syntax of VLOOKUP is very short but complex and the syntax of the IFS function can be extremely long but simple.

Conclusion

Today’s lesson was how to use the IFS function in google sheets. We have learned what the IFS function is. How to use the IFS function in google sheets? and we have seen some practical examples and solved real-life use cases and problems to understand the need for the IFS function in google sheets. Firstly, we find out the grade of the students based on their marks, without IFS it cannot be done with a single formula using IF, although it can be done using VLOOKUP, understanding VLOOKUP can be a little more complicated than IFS function. We then saw how to calculate values using IFS with equal conditions, we calculated the incentives for the employees based on their off-days. Then we saw how to calculate the delivery charges based on the shopping bill. We have covered these three real-world applications to understand the IFS function.

I hope you find this article helpful and that you have learned something new from it. I will see you next time. Take care. Keep learning with Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com
Logo
Enable registration in settings - general