Mastering the IFs formula: simplify conditional logic in Google Sheets

The IFS formula in Google Sheets is ideal for evaluating multiple conditions in a single formula. Whether tracking stock levels, managing sales data, or handling inventory, this formula simplifies complex logical operations and ensures efficient decision-making. 

Let’s explore how the IFS formula works and highlight its practical applications with real-life examples.

IFS formula syntax

The IFS formula evaluates multiple conditions and returns the first result where the condition is TRUE. Unlike IF, which requires nesting for multiple conditions, the IFS formula handles them easily.

• condition1: The first condition to test.
• value_if_true1: The value to return if condition1 is TRUE.
• condition2, value_if_true2, etc.: Additional conditions and their respective results.

Why use the IFS formula?

• Eliminates the need for cumbersome nested IF formulas.
• Handles multiple conditions with clarity and precision.
• Offers flexible, dynamic logic for advanced calculations.

1. Managing inventory restocking

Scenario: Calculate stock status based on specific thresholds.

Formula breakdown:

• Condition 1: B5=0, “Out of Stock”
  • If the value in B5 is exactly 0, the formula returns “Out of Stock”.
• Condition 2: B5<10, “Reorder Soon”
  • If the value in B5 is less than 10 (but not 0), the formula returns “Reorder Soon”.
• Condition 3: B5>=10, “In Stock”
  • If the value in B5 is greater than or equal to 10, the formula returns “In Stock”

Check the formula in the spreadsheet

How it works:

• If the stock level is 0, it returns “Out of Stock”.
• If the stock level is less than 10, it returns “Reorder Soon”.
• Otherwise, it returns “In Stock”.

Benefits:

• Automatically tracks stock status without manual intervention.
• Helps in maintaining optimal inventory levels.

2. Stage-Wise Sales Breakup

Scenario: Categorize sales leads based on their status.

Formula breakdown

• Condition 1: A5=”Target”, “Initial”
• If the value in A5 is “Target”, the formula returns “Initial”.
• Condition 2: A5=”Contract Generated”, “Negotiation”
• If the value in A5 is “Contract Generated”, the formula returns “Negotiation”.
• Condition 3: A5=”Lost”, “Closed – Lost”
• If the value in A5 is “Lost”, the formula returns “Closed – Lost”.
• Condition 4: A5=”Won”, “Closed – Won”
• If the value in A5 is “Won”, the formula returns “Closed – Won”.

Check out the formula in the spreadsheet

How it works

Matches the lead status with predefined stages and returns the corresponding stage.

Benefits

• Provides dynamic categorization for leads, aiding in better sales tracking.
• Simplifies sales pipeline visualization.

3. Student Grading System Based on Scores

Scenario: A teacher wants to assign grades to students based on their test scores using specific criteria.

Formula breakdown

• Condition 1:

• • B5=””, “Missing Data”: If the cell B5 is blank, the formula returns “Missing Data”.

• Condition 2:

• • B5>=85, “A”: If the score is greater than or equal to 85, it returns “A”.

• Condition 3:

• • B5>=70, “B”: If the score is greater than or equal to 70 but less than 85, it returns “B”.

• Condition 4:

• • B5>=60, “C”: If the score is greater than or equal to 60 but less than 70, it returns “C”.

• Condition 5:

• • B5<60, “D”: If the score is less than 60, it returns “D”.

Check out the formula in the spreadsheet

How it works

• The formula starts by checking if B5 is blank. If true, it immediately returns “Missing Data” and stops further evaluation.
• If B5 has a score, it evaluates the score against the conditions in sequential order:
• If B5 >= 85, it assigns “A”.
• If B5 >= 70, it assigns “B” (only if the score doesn’t qualify for “A”).
• If B5 >= 60, it assigns “C” (only if the score doesn’t qualify for “A” or “B”).
• If B5 < 60, it assigns “D”.
• The IFS formula stops evaluating as soon as it finds a condition that is TRUE, ensuring efficiency.
• Handles missing data and assigns appropriate grades automatically, saving time and reducing errors.

Benefits

• Simplifies Logic: Eliminates the need for nested IF statements, making formulas easier to read and manage.
• Handles Multiple Conditions: Efficiently evaluates multiple criteria in a single formula.
• Dynamic and Flexible: Adapts to different scenarios like grading, approvals, or categorization.
• Improves Clarity: Provides clear outputs based on logical conditions, enhancing data interpretation.
• Error-Free Execution: Stops at the first TRUE condition, reducing unnecessary calculations.

4. Conditional Payment Approvals

Scenario: A finance team wants to approve payments based on conditions such as payment amount and supporting documents.

Formula breakdown

• Condition 1:
• C5=”No”, “Pending”: If the documents are not submitted (C5=”No”), the status is “Pending”.
• Condition 2:
• AND(B5<=1000, C5=”Yes”), “Approved”: If the payment amount is less than or equal to $1,000 and documents are submitted, the status is “Approved”.
• Condition 3:
• AND(B5>1000, C5=”Yes”), “Approved”: If the payment amount is greater than $1,000 and documents are submitted, the status is “Approved”.
• Condition 4:
• B5>1000, “Pending”: If the payment amount is greater than $1,000 but documents are not submitted, the status is “Pending”.

Check out the formula in the spreadsheet

How it works

• Checks if the payment amount is missing and returns “Missing Data”.
• If documents are not submitted, it marks the payment as “Pending”.
• Approves payments if the amount is within the limit and documents are submitted.
• Marks large payments as “Pending” if documents are not provided.
• Stops checking once a condition is met and returns the result.

Benefits

• Automates Decisions: Automatically approves or flags payments based on conditions like amount and document submission.
• Saves Time: Eliminates manual checking for approval criteria.
• Reduces Errors: Ensures consistent and accurate approvals by applying the same logic to all payments.
• Dynamic Updates: Automatically adjusts results when payment data or document status changes.

Simplify your workflows with IFs

The IFS formula is a game-changer for handling multiple conditions in Google Sheets. Whether you’re managing inventory, tracking sales, or calculating hours, it eliminates complexity and enhances productivity.

Have a unique use case for the IFS formula? Share it in the comments—we’d love to hear your ideas!