SUMPRODUCT

What it does

SUMPRODUCT(array1, [array2], ...)

SUMPRODUCT(array1, [array2], ...) multiplies corresponding elements of its array arguments and sums the products:

SUM over i of (a1_i * a2_i * ... * ak_i)

• Domain: k arrays of identical shape. Microsoft documents that arrays of differing dimensions give #VALUE!.
• Range: all reals.
• With two arrays this is the inner product x · y — the fundamental bilinear form of Euclidean geometry. Everything the dot product means is available here: SUMPRODUCT(x, x) is the squared norm, SUMPRODUCT(x, y) divided by the product of the norms is the cosine of the angle between the vectors, and SUMPRODUCT(x, y) = 0 says the vectors are orthogonal. Cauchy-Schwarz bounds the result: |x · y| <= ||x|| ||y||.
• With one array it degenerates to SUM. With three or more it is a multilinear contraction — a sum over the diagonal of a k-fold tensor product — which has no standard name in spreadsheet vocabulary but is exactly what the formula says.
• Identities: SUMPRODUCT(x, x) = SUMSQ(x); SUMPRODUCT(x-y, x+y) = SUMX2MY2(x, y); SUMX2PY2(x,y) - 2*SUMPRODUCT(x,y) = SUMXMY2(x,y). The three fixed-shape members of this module are all SUMPRODUCT in disguise.
• Weighted averages: SUMPRODUCT(values, weights) / SUM(weights) is the standard idiom, and is why this function appears in more finance worksheets than any other in the category.

Microsoft documents an alternative calling style in which the commas are replaced by arithmetic operators — SUMPRODUCT((A1:A10)*(B1:B10)) and similar — with the arrays parenthesized to control evaluation order.

Arguments

The commonly misunderstood aspects are two:

1. Shape, not length. Unlike the paired-array trio, SUMPRODUCT compares dimensions. A row range and a column range of the same length are not the same shape, and the documented consequence is #VALUE! — though in modern Excel a row and a column also broadcast into a rectangle under the dynamic-array evaluation rules, which is a different behaviour reached by a different path. Which one applies to a given formula is not settled here.

Try it

Quirks

The conditional-sum idiom

SUMPRODUCT is used far more often as a conditional aggregator than as a dot product, and the mechanism is worth stating because it depends on the coercion rule above.

A comparison over a range produces an array of logicals: (A1:A10>5) is {TRUE;FALSE;...}. Multiplying two such arrays and summing counts the rows where both hold. But logicals inside an array are non-numeric entries under the documented rule, so they must be forced to numbers first. The two idiomatic forms are:

=SUMPRODUCT(--(A1:A10>5), --(B1:B10="x"))
=SUMPRODUCT((A1:A10>5) * (B1:B10="x"))

The double unary minus (--) is a coercion, not a sign trick: the first negation makes the logical a number, the second restores the sign. The multiplication form coerces through the arithmetic operator instead. Both work; the * form additionally makes the AND semantics visible, and + gives OR (with the caveat that overlapping conditions then count twice).

Historically this idiom was the only way to write multi-criteria aggregation, and it is the reason SUMPRODUCT appears in workbooks that have no dot product anywhere in them. SUMIFS, COUNTIFS and AVERAGEIFS supersede it for the common cases and are clearer; but SUMPRODUCT remains strictly more general, because its conditions are arbitrary array expressions rather than criteria strings, and because it can weight as well as filter.

Result and edge cases

Returns Number.

• Dimension mismatch: #VALUE!, as documented.
• A single array: behaves as SUM over it.
• Non-numeric entries: zero, per the documented remark — including text, logicals and, presumably, empty cells, though the documentation does not enumerate them.
• Error values in the data: not addressed by the documented remark. Under the shared coercion discipline an error propagates rather than being treated as zero; the reference engine declares error_collapse_profile: ReductionFold with error_algebra=CanonicalExcelLegacy for this module, which is a fold rather than a mask.
• Full-column references: Microsoft advises against them for performance. The advice is real — the function has no way to know that most of a column is empty until it has looked.

Two family-level divergences, recorded rather than resolved. SUMPRODUCT shares its implementing module with SUMX2MY2, SUMX2PY2, SUMXMY2 and SERIESSUM, and it disagrees with the first three on two documented points at once:

Both rows come from Microsoft's own pages. For a sum, "zero" and "ignored" happen to coincide; for a product they emphatically do not, since a zeroed factor annihilates its whole term. An implementation sharing a scan across the module has to keep these apart deliberately.

Errors

How to implement

Numerical notes

The inner product is one of the most studied kernels in numerical analysis, and its behaviour is completely characterized.

Conditioning. The relative condition number of a dot product is

2 * SUM |x_i y_i|  /  |SUM x_i y_i|

— the ratio of the sum of absolute term magnitudes to the absolute value of the answer. When the terms are all the same sign this is about 2 and the computation is perfectly benign. When the result is small relative to its terms — near-orthogonal vectors, a residual, a balanced portfolio, a centred correlation — the condition number is unbounded and the computed result can have no correct digits at all. This is not an implementation flaw; it is the problem being ill-posed, and no algorithm in working precision can fix it.

The practical statement: SUMPRODUCT is exact-ish when you use it as a weighted total, and dangerous when you use it to detect orthogonality or to compute a residual.

Error bound. For the naive left-to-right evaluation, the computed dot product satisfies the classical bound in Higham: the error is at most about n times the unit roundoff times the sum of absolute term magnitudes. Note what the bound is proportional to — the sum of magnitudes, not the answer. That is the same statement as the condition number above, read from the other side.

The three improvements, in increasing order of cost:

1. Fused multiply-add. Computing each x_i*y_i + acc in one fma removes the rounding of the product entirely, halving the error term at no cost on hardware that has it. It also makes the result depend on whether fma was used, which is a reproducibility hazard — a portable-reproducible implementation has to choose one and pin it.
2. Pairwise summation, which replaces the factor n in the bound by its logarithm, at essentially no cost.
3. Compensated dot product — Ogita, Rump and Oishi's Dot2 and its relatives — which delivers a result as accurate as if the whole computation had been done in doubled precision, and which can therefore return correct digits even in the ill-conditioned cases above. This is what a mathematically-correct flavour of SUMPRODUCT should do.

Order dependence. Because floating-point addition is not associative, the answer depends on the accumulation order. Two implementations that differ only in summation order will disagree in the last bits on almost any real data set. When comparing a SUMPRODUCT against a hand-built SUM(x*y) array formula, expect small differences and do not read them as a defect in either.

Rust code

aggregate(SUMPRODUCT, "SUMPRODUCT", ARRAYS, sumproduct)

/// SUMPRODUCT: its arguments, all of one shape, multiplied entry by entry and the products added
/// from 0, in row-major order. An entry that is not a number is 0, and an error entry is the
/// result. A scalar argument is a 1-by-1 array; an omitted one is #VALUE!. Arguments of different
/// shapes are #VALUE!, decided after every argument is read, so an error in any argument comes
/// first. A reference of several areas is #VALUE! (dc-bd5: SUMPRODUCT((A1,A7))).
///
/// An entry whose factors are all 0 has the product +0, which adds nothing to the total (a zero
/// total's sign aside, which the published +0 removes), so only the entries where some argument
/// holds a number are multiplied, and a whole column costs what the reader visits in it.
fn sumproduct(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(SUMPRODUCT, services);
    Site::finish(products(&site, arguments, services))
}

fn products(
    site: &Site<'_>,
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Step<CalcValue> {
    let mut factors = Vec::with_capacity(arguments.len());
    for (index, argument) in arguments.iter().enumerate() {
        factors.push(factor(site, index, argument, services)?);
    }
    let shape = (factors[0].rows, factors[0].columns);
    if factors.iter().any(|f| (f.rows, f.columns) != shape) {
        return Err(Exit::Error(WorksheetErrorCode::Value));
    }
    let mut next = vec![0usize; factors.len()];
    let mut total = 0.0;
    while let Some(offset) = factors
        .iter()
        .zip(&next)
        .filter_map(|(f, &at)| f.numbers.get(at).map(|&(offset, _)| offset))
        .min()
    {
        let mut entry: Option<f64> = None;
        for (f, at) in factors.iter().zip(next.iter_mut()) {
            let value = match f.numbers.get(*at) {
                Some(&(held, value)) if held == offset => {
                    *at += 1;
                    value
                }
                _ => 0.0,
            };
            entry = Some(match entry {
                None => value,
                Some(running) => times(site, running, value)?,
            });
        }
        total = site.add(total, entry.unwrap_or(0.0))?;
    }
    site.number(total)
}

fn factor(
    site: &Site<'_>,
    index: usize,
    argument: &CalcValue,
    services: &mut EvalServices<'_>,
) -> Step<Factor> {
    let entry = |value: &CalcValue| number_of(site, Held::Numbers, index, Origin::Held, value);
    match argument.core_ref() {
        CoreRef::Reference(reference) => {
            let [area] = reference.areas() else {
                return Err(Exit::Error(WorksheetErrorCode::Value));
            };
            let (first, columns) = (area.first(), u64::from(area.column_count()));
            let cells = reader(site, services, index)?;
            let mut numbers: Vec<(u64, f64)> = Vec::new();
            read_reference(site, cells, reference, index, &mut |_, key, value| {
                let Some(n) = entry(value)? else {
                    return Ok(());
                };
                let offset = u64::from(key.row() - first.row()) * columns
                    + u64::from(key.column() - first.column());
                if numbers.last().is_some_and(|&(last, _)| last >= offset) {
                    // Cells out of row-major order: the reader breaks its contract.
                    return Err(Exit::Fault(FunctionFault::ReferenceUnreadable {
                        id: site.id(),
                        index,
                    }));
                }
                numbers.push((offset, n));
                Ok(())
            })?;
            Ok(Factor {
                rows: u64::from(area.row_count()),
                columns,
                numbers,
            })
        }
        CoreRef::Array(array) => {
            let mut numbers = Vec::new();
            for (offset, element) in array.iter_row_major().enumerate() {
                // An array an element holds (dc-h9u) is no number.
                if element.as_array().is_some() && element.callable_value().is_none() {
                    continue;
                }
                if let Some(n) = entry(element)? {
                    numbers.push((offset as u64, n));
                }
            }
            let shape = array.shape();
            Ok(Factor {
                rows: shape.rows as u64,
                columns: shape.cols as u64,
                numbers,
            })
        }
        CoreRef::Empty | CoreRef::Missing => Err(Exit::Error(WorksheetErrorCode::Value)),
        CoreRef::Number(_) | CoreRef::Text(_) | CoreRef::Logical(_) | CoreRef::Error(_) => {
            Ok(Factor {
                rows: 1,
                columns: 1,
                numbers: entry(argument)?.map(|n| (0, n)).into_iter().collect(),
            })
        }
    }
}

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

• SUMX2MY2, SUMX2PY2, SUMXMY2 — fixed-shape special cases in the same module, with the different documented conventions tabulated above.
• SUMSQ — SUMPRODUCT(x, x).
• SERIESSUM — the module's other member: a power series rather than an inner product, but the same reduce-a-product-list shape.
• SUMIF, SUMIFS, COUNTIFS, AVERAGEIFS — the criteria family that supersedes the conditional-sum idiom for the common cases. They are clearer and generally faster; they are also less general, and they use a criteria-string language rather than array expressions.
• MMULT — matrix multiplication, which is the inner product generalized in the other direction (over pairs of indices rather than over more arrays). SUMPRODUCT(x, y) equals MMULT(TRANSPOSE(x), y) for column vectors, and where a whole matrix of inner products is wanted, MMULT is the right tool.
• SUM with an array formula — SUM((A:A>5)*(B:B)) entered as an array formula does the same job; SUMPRODUCT was for decades the way to get array evaluation without the Ctrl+Shift+Enter ritual, which is a large part of its historical popularity.
• Confused with: SUMIFS (criteria strings, not array expressions), and PRODUCT (which multiplies everything together and does not sum).

Named variants

No named variants are recorded yet.