SUMSQ

What it does

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

SUMSQ(number1, [number2], ...) returns

SUM over i of x_i^2

the sum of the squares of its arguments.

• Domain: any finite list of real numbers.
• Range: [0, +infinity). The result is zero only when every contributing value is zero; it is otherwise strictly positive. SUMSQ of an empty selection is zero.
• Structure: this is the squared Euclidean norm, ||x||^2. It is the quantity that sits under least squares, variance, the chi-square statistic, and every energy or power calculation. SQRT(SUMSQ(...)) is the Euclidean norm itself, and Excel has no separate function for it.
• Identities: SUMSQ(x) = SUMPRODUCT(x, x); SUMSQ(x) - n*mean^2 = DEVSQ(x) is the computational (and numerically unwise — see below) form of the sum of squared deviations; SUMX2PY2(x, y) = SUMSQ(x) + SUMSQ(y).
• Degenerate cases: a single argument gives x^2; SUMSQ(3, 4) gives 25, the documented example, and is the Pythagorean case that explains why the function is useful.

Microsoft's page gives the signature and describes the arguments as numbers, names, arrays or references containing numbers, up to 255 arguments.

Arguments

• A value passed directly — SUMSQ("3", TRUE) — undergoes ordinary to-number coercion.
• A value reached by scanning a range — SUMSQ(A1:A3) where a cell holds the text "3" — is governed by the family's scan policy, which is not the same rule.

Microsoft's article states both sides in its own vocabulary: values "typed into the list" are included, while "if an argument is an array or reference, only numbers in that array or reference are counted", and the function "will ignore empty cells, logical values, text, or error values in the array or reference". The Handbook's shared treatment of this asymmetry is in Coercion and lifting; this page does not restate it.

A divergence worth recording. Microsoft's remark places error values in the ignored set for arrays and references, while the same article separately says errors arise from error values in arguments. The Handbook's call model takes the opposite position from the first clause: "An error in a scanned range does surface from SUM — skipping errors is not part of the ignore-text scan policy." So the documented remark and the Handbook's model of the aggregate family disagree about what an error value inside a scanned range does. Neither has been checked against Excel for SUMSQ in this record, and the disagreement is published rather than silently resolved. It is the first probe listed below.

Try it

Quirks

Result and edge cases

Returns Number, always non-negative.

• No numeric values found: zero. An aggregate over an empty or all-text range returns zero, not an error, which is the family convention.
• Squares overflow before sums do. This is the characteristic edge and it is worth stating plainly: a value near the square root of the largest double squares to the top of the range, and anything above that overflows in a single term even though the mathematical answer would be perfectly representable if the computation were scaled. Conversely, small values square to subnormals and then to zero, so a vector of small entries can produce a sum of squares of zero when the true value is a small positive number.
• Logicals and text follow the dual policy above.
• Arrays: SUMSQ is an aggregate, not a lift kernel — an array argument is consumed by scanning, not applied elementwise.

How to implement

Numerical notes

SUMSQ combines two well-studied numerical problems: squaring and summing. Each has a standard treatment and Excel offers no control over either.

1. Overflow and underflow in the squares. The dynamic range of the squares is twice that of the inputs, in the exponent. A vector whose entries are all around 1e200 has a perfectly representable sum of squares in the mathematical sense — but every individual square overflows. Symmetrically, entries around 1e-200 square to zero. The standard remedy, from Blue's TOMS algorithm and the LAPACK dnrm2 lineage, is scaling: find the largest magnitude m, accumulate SUM (x_i/m)^2, and multiply back by m^2 at the end. Blue's refinement uses three accumulators for small, medium and large entries so that a single pass suffices even when the data spans the whole range. This is textbook, it costs one extra pass or a running maximum, and nothing in SUMSQ's interface tells you whether it is done.

For SUMSQ specifically, the left fold interacts badly with the squaring: squares are all non-negative, so there is no cancellation between terms, but the magnitude spread is doubled, which is exactly the condition under which a naive running sum swallows terms. A sum of squares is therefore one of the better cases for compensated summation and one of the worst for a plain fold.

3. Do not compute variance from SUMSQ. The textbook shortcut variance = (SUMSQ(x) - n * mean^2) / (n - 1) subtracts two large nearly equal quantities and can return a negative variance for data with a large mean and small spread. This is the most famous cancellation example in applied statistics and it is worth repeating on this page because SUMSQ is what makes the shortcut writable. Use DEVSQ, or a two-pass or Welford formulation, and leave SUMSQ for the cases where the sum of squares is itself the answer.

4. What a careful implementation does: scale by the largest magnitude, accumulate pairwise or with compensation, and apply the scale back once at the end. The result differs from a naive left fold, sometimes substantially — which is precisely the four-flavour split this Handbook publishes rather than hides. The Handbook makes no claim about what Excel does internally.

Rust code

aggregate(SUMSQ, "SUMSQ", NUMBERS, sumsq)

/// SUMSQ: each number squared, the squares added from 0.
fn sumsq(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(SUMSQ, services);
    if holds_held_arrays(arguments) {
        return Site::finish(Err(Exit::Error(WorksheetErrorCode::NA)));
    }
    let mut total = 0.0;
    let read = each_number(&site, arguments, services, Held::Numbers, &mut |n| {
        total = site.add(total, times(&site, n, 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

Relationships

• SUMPRODUCT(x, x) — the same value by a different route, and a useful cross-check.
• SUMX2PY2(x, y) — SUMSQ(x) + SUMSQ(y) in one call, over paired arrays. SUMX2MY2 and SUMXMY2 complete that trio; all three are documented with a different mismatch error code than SUMPRODUCT, which is discussed on their pages.
• DEVSQ, VAR.S, VAR.P, STDEV.S — the statistical consumers. Note the warning below about computing them from SUMSQ.
• SQRT(SUMSQ(...)) — the Euclidean norm. There is no HYPOT or NORM function in Excel, so this composition is the idiom, and it inherits the overflow problem described below.
• SUM — the parent aggregate; SUMSQ differs only in squaring each term before adding.
• POWER / ^ — writing SUM(A1:A10^2) as an array formula is the hand-rolled equivalent and is not obliged to agree in the last bit, because it materializes an intermediate array.
• Confused with: SUMSQ versus SUM(...)^2. The sum of squares and the square of the sum are different numbers, and the confusion is common enough to be worth naming.

Named variants

No named variants are recorded yet.