MAXIFS

What it does

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) returns the largest value among the cells of max_range whose correspondingly-positioned cells in every criteria range satisfy the matching criterion.

Written out, with i ranging over positions in the common shape:

S = { i : P₁(criteria_range1[i]) ∧ P₂(criteria_range2[i]) ∧ … ∧ P_k(criteria_range_k[i]) }
MAXIFS = max { max_range[i] : i ∈ S, max_range[i] is numeric }

Two structural facts:

1. The conditions are combined with AND, positionally. Criteria pairs intersect; there is no OR form and no way to write one inside a single call. Position, not value, is what links a criteria cell to a max_range cell — which is why the shapes must match.
2. MAXIFS is a selection stage followed by a reduction. Everything about the selection is shared with the rest of the criteria family; everything about the reduction is shared with MAX. The two halves are worth thinking about separately, because they carry different risks — the selection carries a comparison-tolerance question, the reduction carries the empty-set question.

Microsoft documents the size requirement directly: "The size and shape of the max_range and criteria_rangeN arguments must be the same, otherwise these functions return the #VALUE! error."

Domain and result. max_range may hold anything; only its numeric cells participate in the maximum. The result is a real number. When S is empty — or when no cell of max_range at a selected position is numeric — Microsoft documents that the function returns 0.

Arguments

Ranges must match in shape, not merely in count. A 3×1 max_range against a 1×3 criteria range is a shape mismatch even though both hold three cells.

Try it

Quirks

The criteria language

The criteria argument is the family's shared mini-language, and Microsoft's page says so explicitly: the same criteria format works across MINIFS, SUMIFS and AVERAGEIFS. The Handbook documents the language once, on the criteria-family pages; the parts that bear on MAXIFS specifically are:

• A bare number or text is an equality test.
• A text criterion may begin with a comparison operator — ">5", "<>x", ">="&A1 — and the operator is parsed out of the string.
• Wildcards ? and * apply to text criteria, with ~ as the escape.
• Microsoft documents that a criteria argument referring to an empty cell is treated as 0. That is a sharp edge: MAXIFS(B:B, A:A, C1) with C1 blank does not mean "no filter", it means "equal to zero".

Result and edge cases

Returns Number.

• Non-numeric cells at selected positions are not part of a numeric maximum. Whether they are skipped or cause an error is not stated on Microsoft's page for this function, and the Handbook has not checked.
• Shape mismatch → #VALUE!, documented.
• Whole-column ranges (A:A against B:B) match in shape and are the common idiom; performance is an engine matter the Handbook does not assert on.
• Criteria referring to an empty cell are treated as 0, documented — see above.

Errors

Microsoft's page documents exactly one error condition. It does not document a #NUM!, a #N/A, or an error for the no-match case — the no-match answer is 0.

How to implement

Numerical notes

The reduction half of MAXIFS has no arithmetic and no rounding error, exactly as MAX does not. The numerical substance is entirely in the selection half, and it is genuinely substantial.

The comparison predicate is the whole story. A numeric criterion has to decide whether a stored double equals, exceeds or falls below a target double. Three predicates are plausible and they disagree on real data:

1. Raw IEEE comparison — 0.1+0.2 = 0.3 is FALSE, and the cell is excluded.
2. Truncation to 15 significant decimal digits before comparing — the behaviour the upstream defect stream BUG-FUNC-004 records for Excel's tolerant comparison families. Under this rule 0.1+0.2 and 0.3 are equal and the cell is included.
3. A relative-epsilon comparison — plausible-looking, and not what the record describes.

The difference is not a last-bit matter: it changes which cells are in the population, and therefore can change the returned maximum by an arbitrary amount. A criterion ">=0.3" applied to a column containing 0.1+0.2 either admits a row or does not, and if that row holds the largest value the two answers differ completely. This is the reason MAXIFS belongs on a numerical-behaviour page at all.

A second-order point: because the criteria strings are parsed, a criterion built by concatenation — ">="&A1 — goes through a number-to-text-to-number round trip. Excel's general number formatting renders at most 15 significant digits, so a threshold that needs the 16th or 17th digit to be represented exactly cannot survive the round trip. The safe idiom passes the comparison operator and the value separately where the function allows it, or uses a helper column of booleans; the criteria language does not offer a typed alternative.

The empty-set convention. As on MAX, the documented answer for no match is 0, which is inside the range of plausible data rather than below it. Chained or hierarchical MAXIFS computations over all-negative data inherit the same associativity trap described on the MAX page: the top-level answer can be 0 when every underlying value is negative.

Rust code

conditional(MAXIFS, "MAXIFS", (3, 255), MAX_PAIRS, maxifs)

/// MAXIFS: the largest matched number, or 0 when there is none.
fn maxifs(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(MAXIFS, services);
    Site::finish(
        pairs(&site, arguments, services, true)
            .and_then(|plan| extreme(&site, &plan, services, |n, best| n > best)),
    )
}

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

• MAX — the unconditional maximum. The two differ in more than the filter: MAX takes a variadic value list and applies the direct-versus-scan coercion split, while MAXIFS takes ranges only and applies the criteria family's comparison rules. They are not the same reduction wearing a filter.
• SUMIFS, AVERAGEIFS, COUNTIFS — the rest of the multi-criteria family, sharing the selection stage. COUNTIFS with the same criteria is the standard way to distinguish "no match" from "the maximum is zero".
• SUMIF, AVERAGEIF, COUNTIF — the single-criterion ancestors, whose argument order differs (range first, then criterion, then the sum range last) from the …IFS order (result range first). That reversal is the most common editing mistake when converting between them, and it is silent when the ranges happen to be the same shape.
• DMAX — the database-family conditional maximum, using a criteria range with headers instead of range/criterion pairs. DMAX can express OR, which MAXIFS cannot.
• MAX(IF(…)) — the legacy array-formula idiom for the same job, which uses ordinary worksheet equality rather than the criteria language and therefore does not always agree with MAXIFS. Where they disagree, the criteria language's comparison rules are usually the reason.
• FILTER with MAX — the modern dynamic-array route, which does support OR and arbitrary boolean expressions, and which uses ordinary comparison operators rather than criteria strings.

Named variants

No named variants are recorded yet.