The IFS Google Sheets function lets you easily test multiple conditions in a single formula. While the functions can get long and seem daunting, once you understand them,y ou’ll be applying them fluently. This guide aims to teach you everything you need to know about IFS statements in Google Sheets to get started quickly. Read on to learn more.
What Is the IFS Google Sheets Function?
Google Sheet IFS is known as a conditional function. In this formula, you set some logical tests in a series. There is usually a TRUE value associated with each of the conditions. Google Sheets will perform a logical test on all of the cases. If the first logical test comes as FALSE, then Sheets will check the second case for a match until a match is found. The corresponding value will be displayed when the logical expression returns as TRUE.
This operates like that of nesting numerous IF statements, although it is typically simpler to understand and use. This makes this function perfect when you need to determine a student’s grade based on their performance, compute the commission based on the sales figures, or do any other similar calculations.
IFS Formula Google Sheets
Before we look at the formula in action, let’s look at how the Google Sheet IFS function works. Here is the syntax for the formula:
=IFS(con1, val1, [con2, val2, …])
The formula requires at least two parameters, both of which are required for the IFS statement Google Sheets to work. These parameters are:
- con1: this parameter defines the first condition to be checked by the formula.
- val1: this parameter defines the value which the Google Sheets IFS function will return if the con1 parameter returns as true.
- con2: this parameter is optional and defines the additional conditions you wish to specify on Google Sheet IF multiple conditions.
- val2: the optional value is the value that will be returned if the con2 parameter returns as true. This value becomes a required parameter if a condition parameter is added.
Now that we know the Google Sheets syntax for IFS, let’s look at a few things you should remember when using this formula. These are:
- Only the first TRUE condition’s value would be returned by the IFS function. Consequently, it is possible to have many TRUE conditions. However, just the first one’s value would be returned as output.
- The IFS function’s criteria must all return either TRUE or FALSE. The formula will produce an error if it doesn’t.
- The calculation would produce the #N/A error if every condition in the IFS function returned FALSE. You can use the last Condition as TRUE, and the value as FALSE or a descriptive statement such as “No Match” because the #N/A formula parse error is not particularly helpful in figuring out what occurred.
Google Sheets IFS Formula Examples
Let’s look at some examples of Google Sheets IFS multiple conditions statements.
Example 1 – Logical Test to Calculate Sales Commissions
For this example, we have the sales data for several employees in the company. Suppose you wish to calculate their commission based on their sales. Doing so one by one for every employee can be tedious and time-consuming. Instead, we can use a Google Sheets if statement with multiple conditions to find the commissions for every individual salesperson based on specified data.
Here is how to use the IFS function in Google Sheets:
- In the cell where you wish to add the formula, write an Equal (=) sign to start the formula.
- Enter the keyword for the Google Sheets if multiple conditions formula, which is IFS, and add an opening bracket.
- Here we will start adding the parameters. For this example, we wish to compare the given value with the ones provided in the sales slab. In this case, the first and second conditions will look like B2<E3,F2. This means that if the value in B2 is greater than the value in E3, it will return the value from F2.
- Keep repeating the previous step by adding the parameters to search column E until there is a match.
As we wish to use the formula in several cells, we want some of the values to stay the same when we use the Google Sheets autofill feature. To do this, add a Dollar ($) sign with the cell addresses to make it an absolute reference.
Example 2 – Find Student’s Grade Based on Test Score
In this example, we have the test scores for students in a class, and Google Sheets evaluates and assign them a grade based on their scores. This works in the same way as the previous example.
The formula for this particular example will look like this:
The formula will compare the student’s score with the values in the table towards the right side. Once a match is found, the corresponding grade is displayed.
Frequently Asked Questions
What Can I Use Instead of an IFS Function?
IFS is essentially nested if multiple conditions Google Sheets, so if you wish to use an alternate function, you can utilize the Google Sheets IF function. You can also use the CHOOSE and the VLOOKUP function, but all of these have pros and cons, so make sure to use the one that suits your needs.
What Is the Difference Between IF and IFS in Google Sheets?
You can utilize IF and IFS frequently because they are helpful functions. Sheets IFS verifies more than one condition, whereas IF verifies just one. The IF function allows you to define what value to return if the condition is FALSE, which is the main distinction between it and the IFS function. The IFS function does not allow this natively. When examining several conditions, the IFS formula might be shorter and easier.
The IFS Google Sheets function allows you to test against multiple conditions and return a specified value when TRUE. Once you’ve done this a few times, it will become easy, despite the long formulas.
Want to become a Google Sheets expert? We highly recommend checking out Udemy’s Google Sheets Comprehensive Masterclass and the Complete Google Sheets Course.
- Goal Seek in Google Sheets with What IF Analysis
- Using the IFERROR Function in Google Sheets
- How to Use Conditional Formatting in Google Sheets
- A Guide to Gooogle Sheets Comparison Operators
- How to VLOOKUP From Another Sheet in Google Sheets
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
3 thoughts on “Learn IFS in Google Sheets in 5 Minutes (2 Easy Examples)”
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
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.
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.
Comments are closed.