AVERAGEIFS

What it does

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) averages the numeric cells of average_range at the positions where every criterion holds:

M      = { i : criteria₁(criteria_range₁[i]) ∧ … ∧ criteria_m(criteria_range_m[i]) }
result = ( Σ_{i ∈ M, average_range[i] numeric} average_range[i] )
         / #{ i ∈ M : average_range[i] numeric }

The conjunction is positional, not relational: position (i) in every criteria range refers to the same row of the same conceptual table as position (i) in average_range. This is why the shapes must agree, and it is why AVERAGEIFS cannot express a join.

Microsoft states the rule in one sentence: "Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell."

There is no OR form. Disjunction has to be built as a sum of AVERAGEIFS results — which is wrong for a mean, because means do not add — or by moving to FILTER or SUMPRODUCT. That asymmetry (AND is a function, OR is not) is a real limitation of the whole …IFS family.

The criteria parsing and cell-matching stages are shared verbatim with AVERAGEIF, COUNTIF(S), SUMIF(S), MAXIFS and MINIFS; that section is the single description, and it is not repeated here.

Arguments

The argument order is reversed relative to AVERAGEIF. In AVERAGEIF the tested range comes first and the averaged range is an optional third argument; in AVERAGEIFS the averaged range comes first and is mandatory. Rewriting one as the other by adding a criterion pair silently produces a different formula.

Try it

Quirks

Differences from AVERAGEIF that are not just "more criteria"

The documented logical rule is the row where the two Microsoft pages contradict each other; see the divergences section.

Result and edge cases

Returns Number.

• No position satisfies every criterion → #DIV/0!, documented: "If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value."
• A single criterion pair. AVERAGEIFS(B1:B10, A1:A10, ">5") is the exact-shape analogue of AVERAGEIF(A1:A10, ">5", B1:B10), and is the safer of the two to write precisely because no anchoring can occur.
• Empty cells in average_range contribute to neither sum nor count.
• Overlapping ranges, including using average_range as its own criteria range, are ordinary.
• Arrays. The surface lifts natively (lift_broadcast_profile: surface_native); inline array arguments are accepted but must match shape exactly, since an array carries no reference to re-anchor.

Documentation divergences

3. The #DIV0! spelling appears in the remark text on both pages where #DIV/0! is meant; noted so a reader searching for the code finds the remark.

How to implement

Numerical notes

The reduction is the same plain sequential f64 accumulation and single division as AVERAGE, applied to the masked subset in row-major order. Its error analysis carries over unchanged: a bound proportional to (\sum|x_i|) over the selected cells, and order sensitivity.

Two things are specific to this surface.

Rust code

conditional(
    AVERAGEIFS,
    "AVERAGEIFS",
    (3, 255),
    AVERAGE_PAIRS,
    averageifs,
)

/// AVERAGEIFS: AVERAGEIF over the rows that meet every criterion.
fn averageifs(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(AVERAGEIFS, services);
    Site::finish(
        pairs(&site, arguments, services, true).and_then(|plan| mean(&site, &plan, services)),
    )
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• AVERAGEIF — the singular form, with reversed argument order and the anchoring rule this function drops. The shared criteria language is documented there.
• SUMIFS / COUNTIFS / MAXIFS / MINIFS — the rest of the plural family, same module, same conjunction, same shape rule. AVERAGEIFS is SUMIFS/COUNTIFS only when every matched cell is numeric; when a matched cell holds text the divisors differ, because COUNTIFS counts matched positions and AVERAGEIFS counts matched numeric cells.
• DAVERAGE — the database-family conditional mean. It expresses OR natively, through multiple criteria rows, which is exactly what AVERAGEIFS cannot do; see DAVERAGE.
• FILTER + AVERAGE, or AVERAGE(IF(...)) as an array formula — the routes that can express disjunction and relational conditions. They use ordinary worksheet comparison, so they do not inherit the wildcard language or the tolerant numeric comparison.
• GROUPBY / PIVOTBY — where a whole table of conditional means is wanted rather than one cell.

Named variants

No named variants are recorded yet.