SUM

What it does

SUM(number1, [number2], ...)

SUM(number1, [number2], ...) adds individual numbers and numbers held in ranges or arrays. A range keeps a total tied to the cells that contain the data; mixing a range with further scalar arguments adds all of them.

Try it

=SUM(7,-2,5) returns 10. With B2:B4 containing 7, the text pending, and 5, =SUM(B2:B4) returns 12 because the referenced text is skipped.

Direct constants use a different admission rule: =SUM(1,TRUE,"2") adds one, one and two to give four. Putting those three values in an array, =SUM({1,TRUE,"2"}), admits only the numeric one. A cell's numeric value and its text spelling are different inputs.

Quirks

Referenced text, logical values and blanks are skipped; numeric zero participates. Direct logicals and numeric text convert, while unconvertible direct text causes #VALUE!.

Recorded error controls give direct-argument failures priority over errors held inside ranges or arrays. For example, SUM({1,#REF!},"a") gives #VALUE!. Among held errors, reading order matters. A later worksheet error can also determine the result after the numeric accumulator has overflowed.

Changing the order of large positive and negative terms can change a floating-point sum. Compensation or sorting is a different numerical recipe, even when it is more accurate mathematically.

How to implement

1. Preserve each argument's origin: direct scalar, reference or array. Validate direct arguments through numeric conversion first, in argument order, before processing held errors.
2. Visit arguments in order. For a reference visit areas in order and cells in row order; for an array visit elements in row order. Admit held numbers, skip held text/logicals/blanks, and propagate errors under the priority above.
3. Begin with total=0. For each admitted number x, assign total=R(total+x), where R rounds to a 64-bit significand and then stores to binary64. Keep one store per addition. Publish the final number under the worksheet finite/subnormal policy.

For 7,-2,5, the accumulator is 0 → 7 → 5 → 10. A direct scalar that converts successfully must still be added at its original traversal position; the initial validation pass is not an instruction to sum scalars ahead of ranges. The current implementation also reads arrays boxed inside a GROUPBY result through to their elements. Keep that extension distinct from ordinary rectangular-array collection. The cited source and captures establish these specific routes; no published Handbook suite is supplied here.

Rust code

aggregate(SUM, "SUM", NUMBERS, sum)

/// SUM: the numbers added from 0, in the reading order. The error is the first among the direct
/// arguments, in argument order, a coercion's #VALUE! included, before any error that a reference
/// or an array holds; among those, the first in the reading order. A total that overflows is
/// #NUM!, even when a later number would bring it back, and an error read after the overflow is
/// the result. The error_order rows of cfunc-m1.2's oracle workbook measure each of these orders
/// (with #N/A in B1, #DIV/0! in A2 and numbers in A1 and B2): =SUM(A1:B2,#NULL!) is #NULL!,
/// =SUM(B1,"a") and =SUM({1,#REF!},"a") are #VALUE!, =SUM({1,#REF!},#N/A) and
/// =SUM(#N/A,{1,#REF!}) are #N/A, =SUM({1,#REF!},A1:B2) is #REF! and =SUM(A1:B2,{1,#REF!}) #N/A,
/// and =SUM(9.99999999999999E+307,9.99999999999999E+307,B1) is #N/A.
fn sum(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(SUM, services);
    let mut total = 0.0;
    let read_through = read_through_held_arrays(arguments);
    let arguments: &[CalcValue] = read_through.as_deref().unwrap_or(arguments);
    let held = match read_held_references(&site, services, arguments, HeldReference::FirstCell) {
        Ok(held) => held,
        Err(exit) => return Site::finish(Err(exit)),
    };
    let arguments: &[CalcValue] = held.as_deref().unwrap_or(arguments);
    let read = direct_first(&site, arguments).and_then(|()| {
        each_number(&site, arguments, services, Held::Numbers, &mut |n| {
            total = site.add(total, n)?;
            Ok(())
        })
    });
    Site::finish(read.and_then(|()| site.number(total)))
}

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

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Use SUMIFS for conditions, SUMPRODUCT for weighted products, or SUBTOTAL when filtering and hidden-row rules matter.

Named variants

No named variants are recorded yet.