How to Use the IFS Function in Google Sheets

The IFS function in Google Sheets evaluates multiple conditions and returns the value corresponding to the first true condition. It's particularly useful when you need to check several conditions without nesting multiple IF functions. The IFS function simplifies your formulas and makes your data management tasks more efficient.

Key Features of the IFS Function:

• Multiple Conditions: Handles several conditions in one formula.
• No Nesting: Eliminates the need for multiple closing parentheses, making the formula cleaner.
• Dynamic Results: Provides results based on the first true condition.

IFS Function Syntax

The syntax for the IFS function is as follows:

IFS(condition1, value1, condition2, value2, condition_n, value_n)

Parameters:

• condition1, condition2, condition_n: These are the conditions that you want to evaluate. Each condition is a logical expression that returns either TRUE or FALSE.
• value1, value2, value_n: These are the corresponding values to return if the associated condition is true.

You can include as many conditions and true values as needed.

1. How to Use IFS Function in Google Sheets

To use the IFS Function in Google Sheets follow the steps given below:

Step 1: Open Your Google Sheets Document

Start by opening your Google Sheets document where you want to use the IFS function.

Step 2: Select the Cell

Click on the cell where you want the result of the formula to appear. For this example, click on C2 in the "Performance Category" column.

Step 3: Enter the IFS Formula

Type or paste the IFS formula in the selected cell. Here's an example:

=IFS(B2>=90, "Excellent", B2>=80, "Good", B2>=70, "Average", B2<70, "Needs Improvement")

• Condition 1: B2 >= 90 assigns "Excellent" if the sales are 90 or more.
• Condition 2: B2 >= 80 assigns "Good" if the sales are between 80 and 89.
• Condition 3: B2 >= 70 assigns "Average" if the sales are between 70 and 79.
• Condition 4: B2 < 70 assigns "Needs Improvement" if the sales are below 70.

Example Formula Breakdown:

• If B2 = 120, the formula will return "Excellent".
• If B2 = 80, the formula will return "Good".
• If B2 = 72, the formula will return "Average".
• If B2 = 50, the formula will return "Needs Improvement".

Step 4: Press Enter

After typing the formula, press Enter to apply it. The cell will display the performance category based on the sales data.

Step 5: Drag Formula to Other Cells

To apply the formula to other rows:

• Hover over the bottom-right corner of cell C2 until you see a small square handle (fill handle).
• Drag the handle down to fill the formula for the remaining rows in column C.

Preview Result:

Notes:

• The IFS function evaluates conditions in order, returning the result for the first condition that is true.
• Ensure that the conditions are arranged in descending order so that the function checks the higher values first.

2. Advanced IFS Function in Google Sheets

The Advanced IFS Function in Google Sheets allows you to handle multiple conditions, including blanks and special cases. This is useful when you want to evaluate different criteria in a more flexible and efficient way. Below are the steps for applying the advanced IFS formula to the given data:

Step 1: Select the Cell

Click on the cell where you want the result to appear. For example, click on C2 in the "Performance Category" column, as this is where the performance result will be displayed.

Step 2: Enter the Formula

In the selected cell (C2), type or paste the following IFS formula:

=IFS(
B2>=150, "Excellent",
AND(B2>=100, B2<150), "Good",
AND(B2>=50, B2<100), "Average",
B2=0, "Needs Improvement",
ISBLANK(B2), "No Data"
)

• Condition 1: B2 >= 150 returns "Excellent" if the sales are 150 or more.
• Condition 2: AND(B2 >= 100, B2 < 150) returns "Good" if the sales are between 100 and 149.
• Condition 3: AND(B2 >= 50, B2 < 100) returns "Average" if the sales are between 50 and 99.
• Condition 4: B2 = 0 returns "Needs Improvement" if the sales are 0.
• Condition 5: ISBLANK(B2) returns "No Data" if the sales cell (B2) is blank.

Example Formula Breakdown:

• If B2 = 200, the formula will return "Excellent".
• If B2 = 120, the formula will return "Good".
• If B2 = 80, the formula will return "Average".
• If B2 = 0, the formula will return "Needs Improvement".
• If B2 is blank, the formula will return "No Data".

Step 3: Press Enter

After typing the formula, press Enter to apply it. The cell will display the performance category based on the sales value in B2.

Step 4: Apply the Formula to Other Rows

To apply the formula to other rows:

• Hover over the bottom-right corner of cell C2 until you see a small square handle (fill handle).
• Drag the handle down to fill the formula for the remaining rows in the "Performance Category" column.

Preview Result:

Notes:

• The IFS function evaluates conditions in order, returning the result for the first condition that is true.
• Using the AND function allows you to combine multiple conditions within a single formula.
• The ISBLANK function helps handle blank cells, making the formula more dynamic.

3. Use Cases for the IFS Function in Google Sheets

1. Categorizing Data Based on Conditions

You can use the IFS function to categorize numerical data into specific ranges. For example, classifying students' scores into grades (A, B, C) based on their performance.

Example:

=IFS(A2 >= 90, "A", A2 >= 80, "B", A2 >= 70, "C", A2 < 70, "F")

This formula assigns letter grades based on the value in cell A2.

2. Conditional Formatting

The IFS function can be paired with conditional formatting to automatically change cell colors based on certain conditions. For example, you can color code sales performance (excellent, good, poor) in your sales report.

3. Handling Multiple Logical Tests

The IFS function eliminates the need for nested IF functions, making it easier to manage multiple conditions. For instance, if you need to test multiple eligibility criteria, you can use the IFS function instead of writing several IF statements.

4. Tips for Using the IFS Function Effectively

• Order of Conditions: The IFS function evaluates the conditions in order. Make sure the most specific condition comes first to prevent conflicts in your results.
• Default Condition: If you have a default case (like "Other"), place it last to ensure it catches all remaining cases.
• Avoid Over-complicating: Keep the number of conditions reasonable to ensure your formula remains easy to read and maintain.