IFS function in Google Sheets - Step-by-Step Guide

Introduction

The IFS function helps you check multiple conditions one by one and returns a result for the first true condition. It is useful when you want to avoid many nested IF statements and keep your formulas simple and clear.

When you want to assign grades based on scores, like A for 90 and above, B for 80 and above, and so on.

When you need to categorize sales amounts into different levels like Low, Medium, and High.

When you want to check several conditions in order and return different messages for each.

When you want to avoid writing many IF formulas inside each other, making your sheet easier to read.

When you want to create a simple decision tree in your spreadsheet without complex formulas.

Steps

Step 1: Click

- the cell where you want the result

The cell is selected and ready for typing a formula

Step 2: Type

- the formula bar

You start entering the IFS formula syntax

💡 Start with =IFS(

Step 3: Enter

- formula bar

You add pairs of conditions and results separated by commas, for example: =IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")

💡 Each condition is followed by the result if true

Step 4: Close

- the formula with a parenthesis )

The formula is complete and ready to run

Step 5: Press

- Enter key

The cell shows the result based on the first true condition

Before vs After

Before

Cell A1 contains 85, and cell B1 is empty

After

Cell B1 shows "B" after entering =IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")

Settings Reference

Condition and value pairs

📍 Inside the IFS formula in the formula bar

To check conditions in order and return the matching result

Default: No default, you must provide pairs

Common Mistakes

Not closing the IFS formula with a parenthesis

The formula will show an error because it is incomplete

Always end the formula with a closing parenthesis )

Not providing pairs of conditions and results

IFS requires pairs; missing one causes an error

Make sure each condition has a corresponding result value

Using overlapping conditions without order

IFS stops at the first true condition, so order matters

Put the most specific or highest priority conditions first

Summary

IFS checks multiple conditions in order and returns the result for the first true one.

It simplifies formulas by avoiding many nested IF statements.

Remember to provide condition-result pairs and close the formula with a parenthesis.

Practice

1. What does the IFS function do in Google Sheets?

easy

A. Sorts data alphabetically.

B. Checks multiple conditions and returns the value for the first true condition.

C. Adds numbers in a range.

D. Counts the number of cells with numbers.

Solution

1. Step 1: Understand the purpose of IFS

   The IFS function evaluates multiple conditions one by one.

2. Step 2: Identify what IFS returns

   It returns the value corresponding to the first condition that is true.

3. Final Answer:

   Checks multiple conditions and returns the value for the first true condition. -> Option B

4. Quick Check:

   IFS returns first true condition's value [OK]

Hint: Remember: IFS stops at first true condition [OK]

Common Mistakes:

• Thinking IFS sums values
• Confusing IFS with sorting or counting
• Assuming IFS returns all true values

2. Which of the following is the correct syntax for the IFS function in Google Sheets?

easy

A. IFS(condition1: value1, condition2: value2)

B. IFS(value1, condition1, value2, condition2)

C. IFS(condition1; value1; condition2; value2)

D. IFS(condition1, value1, condition2, value2, ..., TRUE, default_value)

Solution

1. Step 1: Recall IFS syntax format

   IFS requires pairs of conditions and values separated by commas.

2. Step 2: Identify correct option

   IFS(condition1, value1, condition2, value2, ..., TRUE, default_value) correctly shows pairs and includes a default TRUE condition.

3. Final Answer:

   IFS(condition1, value1, condition2, value2, ..., TRUE, default_value) -> Option D

4. Quick Check:

   IFS syntax uses condition-value pairs separated by commas [OK]

Hint: Pairs of condition and value, separated by commas [OK]

Common Mistakes:

• Using semicolons instead of commas
• Swapping condition and value order
• Using colons instead of commas

3. Given the formula =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F") and cell A1 contains 85, what will be the result?

medium

A. "B"

B. "F"

C. "C"

D. "A"

Solution

1. Step 1: Evaluate conditions in order

   Check if A1>90: 85>90 is false, move to next.

2. Step 2: Check second condition

   Check if A1>80: 85>80 is true, so return "B".

3. Final Answer:

   "B" -> Option A

4. Quick Check:

   First true condition returns "B" [OK]

Hint: Check conditions top to bottom, stop at first true [OK]

Common Mistakes:

• Choosing the highest grade instead of first true
• Ignoring order of conditions
• Forgetting TRUE default case

4. Identify the error in this formula: =IFS(A1>50, "Pass", A1<50, "Fail")

medium

A. Conditions are reversed; should be A1<50 first.

B. Quotes around text values are incorrect.

C. Missing default TRUE condition for unmatched cases.

D. IFS cannot compare numbers.

Solution

1. Step 1: Analyze conditions coverage

   Conditions check if A1>50 or A1<50 but miss A1=50 case.

2. Step 2: Identify missing default case

   Without a TRUE default, formula returns #N/A if no condition matches.

3. Final Answer:

   Missing default TRUE condition for unmatched cases. -> Option C

4. Quick Check:

   Always add TRUE default to handle all cases [OK]

Hint: Add TRUE default condition to avoid errors [OK]

Common Mistakes:

• Not including a TRUE default condition
• Assuming all cases covered without equality
• Misunderstanding error messages

5. You want to assign a category based on sales in cell B2: "High" if sales > 1000, "Medium" if sales between 500 and 1000, "Low" if sales between 100 and 499, and "None" if sales below 100. Which IFS formula is correct?

hard

A. =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None")

B. =IFS(B2>1000, "High", B2>500, "Medium", B2>100, "Low", TRUE, "None")

C. =IFS(B2>=1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None")

D. =IFS(B2>1000, "High", B2>499, "Medium", B2>99, "Low", TRUE, "None")

Solution

1. Step 1: Check condition ranges carefully

   High is sales > 1000, Medium is sales between 500 and 1000 inclusive, Low is 100 to 499 inclusive.

2. Step 2: Verify conditions order and boundaries

   =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None") correctly uses >1000, then >=500, then >=100, and finally TRUE for below 100.

3. Final Answer:

   =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None") -> Option A

4. Quick Check:

   Use >= for inclusive ranges and TRUE for default [OK]

Hint: Use >= for lower bounds and TRUE for default catch-all [OK]

Common Mistakes:

• Using > instead of >= causing gaps
• Wrong order of conditions
• Missing TRUE default condition