AVERAGEIF & AVERAGEIFS in Excel – Conditional Average

Sometimes, I don’t feel like finding a normal average.

For example, when I check my export data, I don’t want the average of all destinations.

I only want to know the average containers shipped to destinations where we exported more than 100 containers.

In this kind of situation, the normal AVERAGE function won’t help.

In simple words:

You want the average only when certain conditions are met.

That’s exactly where AVERAGEIF and AVERAGEIFS come in.

They let you calculate the average only for values that match your condition(s).

• AVERAGEIF → one condition
• AVERAGEIFS → multiple conditions

AVERAGEIF – Average with One Condition:

The AVERAGEIF function calculates the average only for numbers that meet one specific condition.

Think of it like:

Average this only if the value matches my rule.

Syntax:

=AVERAGEIF(range, criteria)

Arguments:

• range: The cells to check
• criteria: The rule (“>100”, “East”, “<5000”, etc.)

AVERAGEIF Example: Export Data:

Here is some sample export data:

 Export Table:

Goal:

Find the average containers shipped only to destinations where containers > 100.

 Formula:

=AVERAGEIF(B2:B8, “>100”)

How Excel Calculates:

Values greater than 100 → 120, 150, 200, 180, 220

(120 + 150 + 200 + 180 + 220) ÷ 5 = 174

Result: 174

AVERAGEIFS – Average with Multiple Conditions:

Sometimes you need more than one condition.

For example:

• Average where containers > 100

AND

• Region = Asia

This is only possible using AVERAGEIFS.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)

 Arguments:

• average_range: Values to average
• criteria_range1: The first range to check
• criteria1: The first condition
• Add more condition pairs as needed

Goal:

Find the average containers shipped to Asia,

only where container count is greater than 100.

Formula:

=AVERAGEIFS(B2:B8, B2:B8, “>100”, C2:C8, “Asia”)

How Excel Checks:

Rows where BOTH are true:

• Containers > 100
• Region = Asia

From the table:

• Japan → 180
• China → 220
• Singapore is excluded because it is 95 (<100).

Calculation:

(180 + 220) ÷ 2 = 200

Result: 200

Summary Table:

In Simple Words

• AVERAGE → Average of everything
• AVERAGEIF → Average when one rule is true
• AVERAGEIFS → Average when many rules are true

These functions are perfect for situations where you want to focus only on meaningful data instead of the entire dataset.

They are extremely useful for:

• Export reports
• Sales analysis
• Student marks
• Finance and budgeting
• Inventory and performance tracking