Lesson 1.5 – Advanced Conditional Functions (SUMIFS, COUNTIFS, AVERAGEIFS)

1. What Are Advanced Conditional Functions?

These functions allow calculations with multiple criteria instead of just one.
They are upgraded versions of SUMIF, COUNTIF, and AVERAGEIF.

A. SUMIFS Function

Purpose: Adds numbers that meet two or more conditions.

Syntax:

• sum_range → The range of numbers to add.

• criteria_range1 → The first range to check.

• criteria1 → The first condition.

• Additional criteria ranges & conditions can be added.

Example 1 – Sales for “Apples” in January

Example 2 – Sales above ₹50,000 for “Mangoes”

B. COUNTIFS Function

Purpose: Counts the number of cells meeting multiple criteria.

Syntax:

Example 1 – Count orders for “Apples” in March

Example 2 – Count sales above ₹60,000 in Delhi

C. AVERAGEIFS Function

Purpose: Calculates the average of numbers that meet multiple conditions.

Syntax:

Example – Average sales for “Bananas” in February

D. Key Points & Tips

• Criteria can be numbers, text, cell references, or expressions.

• All criteria must be TRUE for a record to be included (logical AND).

• There’s no OR logic in SUMIFS directly; use SUM of multiple SUMIFS for OR conditions.

• Always match criteria_range size with sum_range to avoid errors.

Practice Questions – Lesson 1.5

1. Write a formula to sum sales for “Oranges” in March above ₹50,000.

2. Count orders in “Mumbai” where quantity is greater than 10.

3. Find the average marks of students in “Science” who scored above 70%.

4. True or False: COUNTIFS can be used for OR conditions.