Averaging Across Conditions With AVERAGEIFS

AVERAGEIFS calculates the mean of values that meet several conditions at once. Instead of averaging every sale, you can ask What was the average East region sale in January?

It completes the trio: SUMIFS totals, COUNTIFS counts, and AVERAGEIFS finds the middle value — all using the same multi-criteria style. If you know one, you almost know all three.

AVERAGEIFS mirrors SUMIFS exactly. The values to average come first, then the condition pairs.

• average_range — the numbers to average
• criteria_range1, criteria1
• criteria_range2, criteria2

Behind the scenes it sums the matching values and divides by how many matched — essentially SUMIFS divided by COUNTIFS, in one clean function.

With column A as Region, column B as Month, and column C as Amount, here's the average East region sale in January.

The amounts in C:C are what we average. The two condition pairs filter the rows down to East and January before the mean is taken.

You can filter the values being averaged by their own size. Suppose you want the average of large East region sales only — those above 100.

Here column C is used both as the average_range and as a criteria_range. AVERAGEIFS is happy to reuse the same column for both roles.

To build a report users can drive, reference cells instead of typing values. Put the region in F1 and a minimum amount in F2.

For the comparison, join the operator to the cell: ">"&F2 means greater than whatever F2 holds. Plain text matches like the region need no operator, just the cell reference.

The biggest gotcha with AVERAGEIFS: if no rows match all your conditions, there's nothing to average, so it returns a #DIV/0! error.

This is different from SUMIFS (which returns 0) and COUNTIFS (which returns 0). An average of zero items is undefined, so Excel raises an error instead of guessing.

Wrap the formula in IFERROR to show a friendly value when nothing matches. Instead of an ugly #DIV/0!, the user sees a dash or a message.

This keeps dashboards looking clean even when a filter combination has no data. Always consider the empty case when averaging filtered data.

An important nuance: AVERAGEIFS ignores blank cells in the average_range — they don't count toward the total or the divisor. But a cell containing 0 is a real number and is included.

If zeros are placeholders for missing data, they'll drag your average down. Consider adding a criterion like C:C, "<>0" to exclude them when appropriate.

Stack as many conditions as you need. With column D as Salesperson, find the average sale that was East, in January, and made by Maria.

Each added pair tightens the filter. Because AVERAGEIFS uses AND logic, only rows passing all three conditions feed into the mean.

Keep the two argument orders straight:

• AVERAGEIF: range, criteria, [average_range] — test range first, average_range optional and last.
• AVERAGEIFS: average_range, range1, crit1, ... — average_range always first.

As with the others, defaulting to the plural ...IFS version gives you one consistent pattern across SUM, COUNT, and AVERAGE.

AVERAGEIFS powers many side-by-side summaries. List region names down column F, then compute each region's average sale with a single fillable formula by locking the criteria column.

With $C:$C as the average_range, $A:$A as the criteria_range, and $F2 as the relative region cell, copying the formula down gives you one average per region instantly.

Recall what makes AVERAGEIFS behave differently from its siblings.

You can now average values across multiple conditions:

• AVERAGEIFS(average_range, range1, crit1, ...) — average_range comes first.
• No matches gives #DIV/0! — guard it with IFERROR.
• Blank cells are skipped, but zeros are included; exclude them with "<>0" if needed.
• Use ">"&F1 for dynamic numeric limits.

Next: handling date ranges inside these criteria functions.