SUMIF

What it does

SUMIF(range, criteria, [sum_range])

SUMIF(range, criteria, [sum_range]) adds the values of sum_range at the positions where range satisfies criteria. Written as a filtered reduction:

M   = { i : P(range_i) }            the matched index set
SUMIF = SUM over i in M of s_i      where s is sum_range, or range itself if omitted

Two things are worth separating in that definition, because the function's whole difficulty lives in the gap between them:

1. The predicate P is applied to range. It is not an arbitrary expression; it is a criteria value in the family's small criteria language — a number, a text string, a comparison written as text (">5"), or a wildcard pattern.
2. The values summed come from sum_range, a different range, addressed positionally rather than by intersection. That indirection is the source of the sizing rule below.

Microsoft describes the arguments as the range "you want evaluated by criteria", the criteria "in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added", and sum_range as "the actual cells to add, if you want to add cells other than those specified in the range argument".

Arguments

The criteria language, as documented. Criteria may be a number, an expression, a cell reference, text, or a function. Text criteria and any criteria containing logical or mathematical symbols must be enclosed in double quotation marks — so ">5" is a string that the function parses as a comparison, not a comparison the formula evaluates. Wildcards are available: ? matches a single character, * matches any sequence, and ~ escapes a literal ? or *.

A documented limitation worth quoting for its unusual candour. Microsoft states: "The SUMIF function returns incorrect results when you use it to match strings longer than 255 characters." Not an error value — incorrect results. That is a documented silent-wrong-answer condition, and it is rare enough in Microsoft's function reference to be worth flagging: a formula matching long strings is documented to produce a number that is simply not the right number, with no diagnostic anywhere. Any workbook matching on long free-text keys is in that territory.

Try it

Quirks

The sum_range sizing rule

This is the position most readers get wrong, and the documentation is explicit about it: "Sum_range should be the same size and shape as range. If it isn't, performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range."

In other words: sum_range is treated as an anchor, not as an extent. Only its top-left cell is honoured; the shape is taken from range. SUMIF(A1:A10, ">5", B1) therefore sums B1:B10, and SUMIF(A1:A10, ">5", B1:B3) also sums B1:B10 — silently reading seven cells the author never named.

The consequences are worth stating plainly, because none of them produce an error:

• A sum_range that is shorter than range reads past its own end into whatever lies below.
• A sum_range whose orientation differs from range — a row where range is a column — is re-shaped to range's dimensions from its anchor, so the cells summed may have no relation to the ones written in the formula.
• Inserting or deleting rows inside range changes which cells sum_range covers, without touching the sum_range reference at all.

The defensive practice is simply to write both ranges with identical shapes and to keep them adjacent, so a structural edit moves both together.

Result and edge cases

Returns Number.

• No matches: zero. SUMIF does not report an empty match set as an error, which means an entirely mistyped criterion is indistinguishable from a genuine zero total.
• Text and logicals in sum_range: not addressed on the SUMIF page. The aggregate family's range-scan policy is the governing rule; see Coercion and lifting. The Handbook has not checked which scan policy this surface uses.
• Numeric criteria matching is not exact IEEE equality. This is the substantive finding on this surface and it is discussed under Numerical notes.

Errors

Microsoft's page documents no error conditions for SUMIF. It documents a wrong-answer condition instead (the long-string limitation above), which is a different and arguably worse thing.

How to implement

Numerical notes

SUMIF has two numerical characters, and they are unrelated to each other.

What that means for a reader of SUMIF: a criterion of 0.3 can match a stored value of 0.1+0.2, even though those are different doubles. Whether you regard that as a bug or as the only humane behaviour for a spreadsheet, it is a real semantic property and it changes which cells enter the sum. It also means the matched set is not determined by the bits alone, so two implementations that agree on IEEE arithmetic can still disagree on SUMIF.

The record's counts are not this surface's counts. EV-STRUCT-0001 carries an explicit reader warning that its total is over hand-picked lanes spanning both arms, not a pass rate for any surface, and it records an unresolved discrepancy in the upstream lane enumeration. SUMIF appears there with the subject role body-only. This page states none of those figures; the evidence layer renders them beside it with their warnings attached.

Rust code

conditional(SUMIF, "SUMIF", (2, 3), SUM_RANGE, sumif)

/// SUMIF: the numbers of the sum range, or of the range when it is omitted, in the rows whose
/// range cell meets the criterion, added from 0. A total that overflows is #NUM!.
fn sumif(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(SUMIF, services);
    Site::finish(
        single(&site, arguments, services, true).and_then(|plan| total(&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

• SUMIFS — the multi-criterion successor, and note the argument order is different: SUMIFS(sum_range, criteria_range1, criteria1, ...) puts the summed range first, where SUMIF puts it last. Converting one to the other by adding a criterion pair does not work; the arguments have to be rearranged. SUMIFS also requires its ranges to match in shape rather than reshaping from an anchor, which makes it the safer function even for a single criterion.
• COUNTIF / COUNTIFS — the same predicate machinery with a counting reduction. COUNTIF is a co-subject of the evidence record attached to this page.
• AVERAGEIF / AVERAGEIFS, MAXIFS, MINIFS — the rest of the module.
• SUMPRODUCT — the general alternative, whose conditions are arbitrary array expressions rather than criteria strings. It is more general and less readable; SUMIF is the right tool when the condition fits the criteria language.
• FILTER + SUM — the modern dynamic-array route, which makes the matched set visible as a value rather than hiding it inside the aggregation.
• DSUM — the database-family equivalent, with criteria expressed as a range rather than as a string.
• Confused with: SUMIFS (argument order), and with SUMIF over a sum_range of the wrong shape (the sizing rule above).

Named variants

No named variants are recorded yet.