Square every value and add them up — the basis of variance, distance, and least-squares math. SUMSQ does it in one function, no helper column of squares.
The example
3 and 4 → 9 + 16 = 25.
| A | B | |
|---|---|---|
| 1 | Values | Sum of squares |
| 2 | 3, 4 | 25 |
The formula
The formula:
How it works
How it works:
SUMSQ(range)squares each value and sums the results.- It’s the engine behind variance, Euclidean distance, and least-squares fitting.
- Equivalent to
SUMPRODUCT(range, range)orSUM(range^2)(the latter as an array). - For the distance between two points, square the differences:
=SQRT(SUMSQ(x2-x1, y2-y1)).
Sum of squared errors — the heart of regression — is SUMSQ(actual − predicted) as an array, or SUMPRODUCT((actual-predicted)^2). SUMSQ keeps these formulas short and readable.
Try it: interactive demo
Values.
Variations
Euclidean distance
Between two points:
SUMPRODUCT version
Same result:
Sum of squared errors
Regression:
Pitfalls & errors
Text is ignored. SUMSQ skips text and blanks — keep the range numeric.
Squares grow fast. Large values produce very large sums; watch for scale.
Not the same as (sum)². Sum of squares ≠ square of the sum.
Practice workbook
Frequently asked questions
How do I sum the squares of values in Excel?
How do I calculate distance between two points?
Is SUMSQ the same as squaring the sum?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class