|
Getting your Trinity Audio player ready...
|
When working with spreadsheets, logic plays a big role in how we make decisions. Whether you’re preparing a report, verifying data entries, calculating performance scores, or setting conditions for results — logical and conditional functions in Zoho Sheets make it all possible.
These functions help you build rules like —
👉 “If Sales > Target, show Achieved”
👉 “If Leave Days > 3, show Warning”
👉 “If the value is missing, show ‘Not Available’ instead of error”
In this article, we’ll explore the most powerful and practical logical functions — from simple IF() statements to advanced combinations like SUMIFS(), COUNTIFS(), and SWITCH() — all with real-world examples.
1. IF() Function
Syntax:
=IF(logical_test, value_if_true, value_if_false)
Explanation:
The IF() function checks whether a condition is TRUE or FALSE, and returns different results accordingly.
Example 1: Sales Target Status
| Employee | Sales | Target | Result |
|---|---|---|---|
| Ramesh | 52000 | 50000 | Achieved |
| Priya | 48000 | 50000 | Not Achieved |
Formula:
=IF(B2 >= C2, "Achieved", "Not Achieved")
Example 2: Leave Approval
=IF(A2 > 3, "Manager Approval Needed", "Auto Approved")
2. IF with AND() / OR()
Syntax:
=IF(AND(condition1, condition2), value_if_true, value_if_false)
=IF(OR(condition1, condition2), value_if_true, value_if_false)
Read More: IF Function with AND & OR
Example 1: Bonus Eligibility (AND)
| Employee | Sales | Attendance | Result |
|---|---|---|---|
| Ravi | 60000 | 95% | Eligible |
| Sneha | 58000 | 85% | Not Eligible |
Formula:
=IF(AND(B2>55000, C2>90%), "Eligible", "Not Eligible")
Here both conditions must be true.
Example 2: Warning (OR)
=IF(OR(B2<40, C2<50), "Needs Attention", "Good Performance")
If either marks or attendance are low, show a warning.
3. SWITCH() Function
Syntax:
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
Explanation:
The SWITCH() function checks an expression against multiple possible values — a cleaner way than nested IFs.
Example: Department Codes
| Emp ID | Dept Code | Department |
|---|---|---|
| E01 | HR | Human Resource |
| E02 | IT | Information Tech |
| E03 | FN | Finance |
Formula:
=SWITCH(B2, "HR", "Human Resource", "IT", "Information Tech", "FN", "Finance", "Other")
Read More: IFS & SWITCH Function
4. IFERROR() Function
Syntax:
=IFERROR(formula, value_if_error)
Explanation:
This function helps handle errors gracefully. When a formula returns an error (like #DIV/0!), IFERROR displays a custom message instead.
Example 1: Division Error Handling
=IFERROR(A2/B2, "Check Value")
Example 2: Missing Lookup Data
=IFERROR(VLOOKUP(A2, Data!A:B, 2, FALSE), "Not Found")
5. SUMIF() / SUMIFS()
Syntax:
=SUMIF(range, criteria, sum_range)
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example 1: SUMIF – Single Condition
| Region | Sales |
|---|---|
| East | 25000 |
| West | 30000 |
| East | 18000 |
Formula:
=SUMIF(A2:A4, "East", B2:B4)
Result: 25000 + 18000 = 43000
Example 2: SUMIFS – Multiple Conditions
| Region | Month | Sales |
|---|---|---|
| East | Jan | 12000 |
| East | Feb | 15000 |
| West | Jan | 18000 |
Formula:
=SUMIFS(C2:C4, A2:A4, "East", B2:B4, "Feb")
👉 Adds only February sales from East region.
Read More: SUMIF & SUMIFS Function
6. COUNTIF() / COUNTIFS()
Syntax:
=COUNTIF(range, criteria)
=COUNTIFS(range1, criteria1, [range2, criteria2], ...)
Example 1: COUNTIF – Status Check
| Status |
|---|
| Approved |
| Pending |
| Approved |
Formula:
=COUNTIF(A2:A4, "Approved")
Result: 2
Example 2: COUNTIFS – Conditional Count
| Department | Status | Count |
|---|---|---|
| HR | Approved | |
| HR | Pending | |
| IT | Approved |
Formula:
=COUNTIFS(A2:A4, "HR", B2:B4, "Approved")
Result: 1
Read More: COUNTIF & COUNTIFS Function
7. AVERAGEIF() / AVERAGEIFS()
Syntax:
=AVERAGEIF(range, criteria, average_range)
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example 1: AVERAGEIF – Employee Ratings
| Employee | Department | Rating |
|---|---|---|
| Riya | HR | 4.5 |
| Aman | IT | 4.2 |
| Neha | HR | 3.8 |
Formula:
=AVERAGEIF(B2:B4, "HR", C2:C4)
👉 Average rating for HR = (4.5 + 3.8) / 2 = 4.15
Example 2: AVERAGEIFS – Sales Performance
=AVERAGEIFS(D2:D10, B2:B10, "East", C2:C10, ">50000")
Calculates average sales from East region with sales over ₹50,000.
Read More: AVERAGEIF & AVERAGEIFS Function
8. IS Functions (Check Data Type & Logic)
These functions help identify the type or status of data in a cell.
| Function | Purpose | Example |
|---|---|---|
| ISODD(number) | Returns TRUE if number is odd | =ISODD(5) → TRUE |
| ISEVEN(number) | Returns TRUE if number is even | =ISEVEN(8) → TRUE |
| ISNUMBER(value) | Checks if cell contains a number | =ISNUMBER(A2) |
| ISTEXT(value) | Checks if cell contains text | =ISTEXT(B2) |
| ISLOGICAL(value) | Checks if cell contains TRUE/FALSE | =ISLOGICAL(A1) |
| ISFORMULA(reference) | Returns TRUE if a cell contains formula | =ISFORMULA(A1) |
Read More: Other Useful Functions
Real-Life Example: Data Validation
Imagine you receive a dataset from multiple branches — some cells have text instead of numbers.
You can use:
=IF(ISNUMBER(B2), "Valid", "Invalid Data")
👉 Automatically flags incorrect entries.
Real-Life MIS Scenario: Employee Bonus Report
| Emp Name | Department | Sales | Attendance | Bonus |
|---|---|---|---|---|
| Rajesh | Sales | 80000 | 95 | |
| Neha | Sales | 60000 | 85 | |
| Kiran | HR | 70000 | 92 |
Formula:
=IF(AND(C2>=70000, D2>=90), "Eligible", "Not Eligible")
👉 Only employees meeting both sales and attendance criteria get a bonus.
9. Combining Logical & Conditional Functions
Example: Target Achievement Check
=IFERROR(
IF(AND(SUMIF(Region,"East",Sales)>50000, AVERAGEIF(Region,"East",Sales)>15000),
"Target Met", "Below Target"), "Data Error")
👉 Checks multiple conditions and handles any errors gracefully.
10. Nested IF Example
Student Grade Calculation
| Marks | Grade |
|---|---|
| 85 | |
| 72 | |
| 60 | |
| 45 |
Formula:
=IF(A2>=80, "A", IF(A2>=70, "B", IF(A2>=60, "C", "Fail")))
👉 Assigns grades based on marks.
Pro Tip:
When your conditions get complex, replace nested IF with SWITCH() for clarity or use LOOKUP tables for scalable grading.
Zoho Sheet Advantages with Logical Functions
- Zia Assistant can suggest logical formulas automatically.
- Built-in Error Indicators highlight cells that fail logical checks.
- You can create conditional formatting using logical formulas like
=AND(B2>50000, C2>90)to color results dynamically.
Conclusion
Logical and conditional functions in Zoho Sheets make it easy to automate decisions and control how your data behaves.
Whether you’re managing reports, verifying data, or preparing dashboards, mastering these functions helps you turn raw data into meaningful insights.
Start small — use IF() and IFERROR() — then move up to SUMIFS() and combined logic for complex conditions.
These skills are not just useful in Zoho Sheets, but also essential for MIS, reporting, and business analytics roles.
Key Takeaways
- IF(), AND(), OR() handle decision-making logic.
- SUMIFS(), COUNTIFS(), AVERAGEIFS() manage conditional analysis.
- IS functions help validate data types and detect errors.
- Combine logic for dynamic reports and use IFERROR for cleaner results.
What’s Next?
In the next post, we’ll learn about the Lookup Functions in Zoho Sheets