Advanced Conditional Calculation Practice Questions with Solutions

Introduction

Advanced conditional calculations help you calculate values only when specific conditions are satisfied. In practical Excel work, you may need to calculate commissions, bonuses, discounts, tax amounts, incentives, expenses, or performance values based on several conditions at the same time. In this chapter, you will practice SUMIFS, COUNTIFS, AVERAGEIFS, SUMPRODUCT, IF, AND, OR, MAXIFS, MINIFS, and combined formulas through different problems. Advanced Conditional Calculation practice questions with solutions to help you understand the concepts.

Question 1: Calculate Sales Commission Based on Multiple Conditions

Problem Statement

Calculate the commission for each salesperson using these rules:

• Sales of ₹1,00,000 or more and rating of 4.5 or above → 10% commission
• Sales of ₹75,000 or more and rating of 4.0 or above → 7% commission
• Sales of ₹50,000 or more → 5% commission
• Otherwise → 2% commission

Excel Data

Excel Solution

In D2, enter:

=IF(AND(B2>=100000,C2>=4.5),B2*10%,IF(AND(B2>=75000,C2>=4),B2*7%,IF(B2>=50000,B2*5%,B2*2%)))

Copy the formula down.

Expected Output

Concepts Covered

• Nested IF
• AND
• Conditional percentage
• Tiered calculation

Question 2: Calculate Bonus Only for Eligible Employees

Problem Statement

An employee receives a bonus only when:

• Experience is at least 3 years.
• Performance rating is "Good" or "Excellent".

If eligible, the employee receives 8% of salary. Otherwise, the bonus is zero.

Excel Data

Excel Solution

In E2, enter:

=IF(AND(C2>=3,OR(D2="Good",D2="Excellent")),B2*8%,0)

Expected Output

Concepts Covered

• IF
• AND
• OR
• Conditional calculation

Question 3: Calculate Department-Wise Sales Above a Threshold

Problem Statement

Calculate the total sales generated by the IT department where each individual sales transaction is at least ₹50,000.

Excel Data

Excel Solution

Use:

=SUMIFS(C2:C9,B2:B9,"IT",C2:C9,">=50000")

Expected Output

205000

The qualifying IT sales are:

• ₹65,000
• ₹85,000
• ₹55,000

Concepts Covered

• SUMIFS
• Multiple criteria
• Conditional aggregation
• Threshold-based calculation

Question 4: Calculate Average Sales for Active Employees

Problem Statement

Calculate the average sales for employees who satisfy both conditions:

• Status is "Active"
• Sales are at least ₹50,000

Excel Data

Excel Solution

Use:

=AVERAGEIFS(C2:C8,B2:B8,"Active",C2:C8,">=50000")

Expected Output

64000

The qualifying sales are:

• ₹65,000
• ₹72,000
• ₹55,000

Concepts Covered

• AVERAGEIFS
• Multiple conditions
• Conditional average

Question 5: Calculate Discount Based on Quantity and Customer Type

Problem Statement

Calculate the discount amount using these rules:

• Premium customer + quantity ≥ 20 → 15%
• Premium customer + quantity < 20 → 10%
• Regular customer + quantity ≥ 20 → 8%
• Regular customer + quantity < 20 → 5%

Excel Data

Excel Solution

In E2, enter:

=IF(AND(B2="Premium",C2>=20),D2*15%,IF(B2="Premium",D2*10%,IF(C2>=20,D2*8%,D2*5%)))

Expected Output

Question 6: Calculate Weighted Sales Using SUMPRODUCT

Problem Statement

A company wants to calculate total revenue from products using:

Quantity × Unit Price

Calculate the total revenue only for products belonging to the "Electronics" category.

Excel Data

Excel Solution

Use:

=SUMPRODUCT((B2:B7="Electronics")*C2:C7*D2:D7)

Expected Output

102900

Concepts Covered

• SUMPRODUCT
• Conditional calculation
• Multiplication across ranges
• Category-based analysis

Question 7: Find the Highest Sale for a Specific Region and Product

Problem Statement

Find the highest sales amount where:

• Region = "North"
• Product = "Laptop"

Excel Data

Excel Solution

Use:

=MAXIFS(C2:C8,A2:A8,"North",B2:B8,"Laptop")

Expected Output

105000

Concepts Covered

• MAXIFS
• Multiple criteria
• Conditional maximum

Question 8: Calculate Conditional Tax

Problem Statement

Calculate tax based on annual income:

• Income below ₹5,00,000 → 0%
• ₹5,00,000 to below ₹10,00,000 → 10%
• ₹10,00,000 or more → 20%

Excel Data

Excel Solution

In C2, enter:

=IF(B2<500000,0,IF(B2<1000000,B2*10%,B2*20%))

Expected Output

This is an Excel practice example, not a representation of current Indian tax rules.

Question 9: Calculate Conditional Expense Total Using SUMPRODUCT

Problem Statement

Calculate total expenses for:

• Department = "Marketing"
• Expense amount greater than ₹5,000

Excel Data

Excel Solution

Use:

=SUMPRODUCT((A2:A8="Marketing")*(C2:C8>5000)*C2:C8)

Expected Output

35000

Qualifying expenses:

• Advertising → ₹12,000
• Events → ₹15,000
• Software → ₹8,000

Total = ₹35,000

Concepts Covered

• SUMPRODUCT
• Multiple conditions
• Greater-than criteria
• Conditional aggregation

Question 10: Calculate a Dynamic Performance Incentive

Problem Statement

Calculate an employee incentive using three conditions:

• Sales ≥ ₹1,00,000 and rating ≥ 4.5 → 12% of sales
• Sales ≥ ₹75,000 and rating ≥ 4.0 → 8% of sales
• Sales ≥ ₹50,000 → 5% of sales
• Otherwise → 0%

Then round the incentive to the nearest whole number.

Excel Data

Excel Solution

In D2, enter:

=ROUND(IF(AND(B2>=100000,C2>=4.5),B2*12%,IF(AND(B2>=75000,C2>=4),B2*8%,IF(B2>=50000,B2*5%,0))),0)

Copy the formula down.

Expected Output

Concepts Covered

• IF
• AND
• ROUND
• Multiple conditions
• Tiered calculation
• Percentage calculation

Key Takeaways

• Conditional calculations allow Excel to calculate values only when specific conditions are met.
• SUMIFS, COUNTIFS, and AVERAGEIFS are useful for conditional aggregation.
• MAXIFS and MINIFS can find extreme values based on multiple criteria.
• IF can create different calculations for different conditions.
• AND requires all specified conditions to be TRUE.
• OR requires at least one specified condition to be TRUE.
• SUMPRODUCT is useful for advanced conditional calculations across multiple ranges.
• Nested IF formulas can create tiered calculations such as commissions and incentives.
• ROUND can be combined with conditional formulas when the final result needs a whole number.
• Conditional calculations are commonly used for sales commissions, bonuses, discounts, taxes, incentives, expenses, and performance analysis.

FAQs

1. What is an advanced conditional calculation in Excel?

An advanced conditional calculation performs a calculation only when one or more conditions are satisfied.

For example:

=IF(B2>=50000,B2*10%,0)

The formula calculates 10% only when the value is at least 50,000.

2. What is the difference between IF and SUMIFS?

IF is generally used to return different results depending on a condition.

SUMIFS is used to add values that satisfy multiple criteria.

Example:

=IF(B2>=50000,B2*10%,0)

and:

=SUMIFS(C2:C100,A2:A100,"IT",C2:C100,">=50000")

3. Can I use AND inside IF?

Yes.

=IF(AND(B2>=50000,C2="Active"),B2*10%,0)

Both conditions must be TRUE for the calculation to occur.

4. Can I use OR inside IF?

Yes.

=IF(OR(B2="Premium",C2>=20),D2*10%,0)

The calculation occurs when at least one condition is TRUE.

5. Why is SUMPRODUCT useful for conditional calculations?

SUMPRODUCT can evaluate multiple conditions and perform calculations across ranges without requiring helper columns.

For example:

=SUMPRODUCT((A2:A20="IT")*(B2:B20>=50000)*B2:B20)

6. Can SUMIFS use different types of conditions together?

Yes. You can combine text, numbers, dates, and comparison operators.

For example:

=SUMIFS(D2:D100,A2:A100,"IT",B2:B100,">=50000",C2:C100,"Active")

7. How can I calculate different commission rates based on sales?

You can use nested IF conditions.

For example:

=IF(B2>=100000,B2*10%,IF(B2>=50000,B2*5%,B2*2%))

8. Can I combine ROUND with IF?

Yes.

=ROUND(IF(B2>=50000,B2*7.5%,0),0)

This first performs the conditional calculation and then rounds the result.

9. What should I use when I have many conditions?

For a small number of conditions, nested IF can work well. For many fixed categories, IFS or SWITCH may make the formula easier to read. For aggregation, functions such as SUMIFS, COUNTIFS, and AVERAGEIFS are often more appropriate.

10. Are conditional calculations useful in real Excel work?

Yes. They are widely used for:

• Sales commissions
• Employee bonuses
• Discounts
• Incentives
• Expense analysis
• Performance reports
• Financial calculations
• Business dashboards
• Data analysis

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar