AVERAGEIF

What it does

AVERAGEIF(range, criteria, [average_range])

AVERAGEIF(range, criteria, [average_range]) builds a selection mask over range by testing each cell against criteria, and returns the arithmetic mean of the numeric cells of average_range at the selected positions:

M      = { i : criteria(range[i]) }
result = ( Σ_{i ∈ M, average_range[i] numeric} average_range[i] )
         / #{ i ∈ M : average_range[i] numeric }

Two counts are in play and they are not the same. The mask counts matching positions; the divisor counts matching positions holding a number. A matched cell that is empty, text, or logical contributes to neither numerator nor divisor. That is why AVERAGEIF can return #DIV/0! on a range where the criterion matched plenty of cells.

The function decomposes cleanly into three independent stages, and every difficult question about it belongs to exactly one of them:

1. Criteria parsing — turning the criteria argument into a comparison operator plus an operand.
2. Cell matching — applying that comparison to each cell of range.
3. Filtered reduction — averaging the selected cells of average_range.

Arguments

Microsoft describes range as "One or more cells to average, including numbers or names, arrays, or references that contain numbers", criteria as "The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged", and average_range as "The actual set of cells to average. If omitted, range is used."

Try it

Quirks

The criteria mechanism

Matching is kind-aware. A numeric criterion matches numeric cells, and also text cells whose content parses as that number; it does not match logicals. A text criterion matches only text cells. A logical criterion matches logical cells, and text that reads as a logical. This means AVERAGEIF(A:A, 1) and AVERAGEIF(A:A, TRUE) select different cells even though TRUE coerces to 1 in arithmetic.

The average_range anchoring rule

Microsoft states it directly: "The average_range does not need matching dimensions as range; calculations begin from the top-left cell and correspond to range dimensions."

So AVERAGEIF(A1:A10, ">5", B1) averages B1:B10, not the single cell B1. The third argument behaves as an anchor, not as a range: only its top-left corner is load-bearing, and the shape is taken from range.

This is a genuine trap, and it is the reason AVERAGEIFS — which requires exact shape agreement — is not simply "AVERAGEIF with more criteria". Moving a formula from one to the other can change which cells are averaged without changing any argument text.

Result and edge cases

Returns Number.

• No matching cells → #DIV/0!, documented by Microsoft: "If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value."
• Empty cells in average_range are ignored; Microsoft states this.
• range and average_range overlapping or identical is ordinary and is the default.
• Arrays. The surface lifts natively (lift_broadcast_profile: surface_native).

Documentation divergences

The Handbook exists partly to publish these. Three are visible on this surface without running Excel at all.

3. The #DIV0! spelling. Microsoft's remark text renders the error as #DIV0! on both the AVERAGEIF and AVERAGEIFS pages, while the same pages use #DIV/0! elsewhere. This is a typographical defect in the documentation rather than a behavioural claim, but it is worth recording so that a reader searching for the error code finds the remark.

How to implement

Numerical notes

The reduction is a plain sequential f64 accumulation followed by one division, so the error analysis is exactly AVERAGE's: a bound proportional to (\sum|x_i|), a condition number of (\sum|x_i|/|\sum x_i|), and sensitivity to summation order. Nothing about the criteria stage improves it.

What the criteria stage adds is a second numerical question that has nothing to do with summation: the tolerant comparison. Because compare_excel_numbers works on a 15-significant-digit truncation rather than the full 53-bit significand, the selection mask itself is a function of a rounding decision. Two numbers that differ in the sixteenth digit select together. For a conditional average this is a bigger effect than any summation error: a mis-selected cell changes the answer at full magnitude, while a summation error changes it in the last bits. Anyone auditing an AVERAGEIF should look at the mask before looking at the arithmetic.

Rust code

conditional(AVERAGEIF, "AVERAGEIF", (2, 3), AVERAGE_RANGE, averageif)

/// AVERAGEIF: the matched numbers added from 0 and divided by how many there are; #DIV/0! when
/// there is none.
fn averageif(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(AVERAGEIF, services);
    Site::finish(
        single(&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

• AVERAGEIFS — the plural form. Not a superset: it requires exact shape agreement, so it cannot express the anchoring shorthand, and its documentation states the opposite logical rule. Argument order is also reversed — the averaged range comes first.
• COUNTIF / SUMIF — the same criteria mechanism with a different reduction. AVERAGEIF is mathematically SUMIF/COUNTIF only when every matched cell is numeric; otherwise the divisors differ, because COUNTIF counts matched cells while AVERAGEIF counts matched numeric cells.
• MAXIFS / MINIFS — same module, same criteria mechanism, different reduction; note they return 0 rather than an error when nothing matches, which AVERAGEIF does not.
• DAVERAGE — the database-family conditional mean, with a criteria range rather than a criteria expression; see DAVERAGE for that mechanism.
• FILTER + AVERAGE — the modern dynamic-array route, which uses ordinary worksheet comparison rather than the criteria language and therefore does not inherit the wildcard or the tolerant-numeric rules.

Named variants

No named variants are recorded yet.