How to calculate the sum of squares in Excel
Formula (Excel and Google Sheets)
=SUMSQ(B2:B7)Use SUMSQ: =SUMSQ(B2:B7) squares each value and adds the squares.
- Result on the example
- 44.82 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMSQ squares each reading (2.4² = 5.76, 3.1² = 9.61, …) and adds the results. It ignores blank cells and text, like SUM does.
=SUMSQ(B2:B7)Result44.82
B2:B76 rows × 1 col: 2.4, 3.1, 1.8, 2.9, …A range: 6 cells in one column.
SUMSQ(B2:B7)44.82SUMSQ: Sum of squares.- number1
B2:B76 rows × 1 col: 2.4, 3.1, 1.8, 2.9, …
- number1
In Google Sheets
The same formula works in Google Sheets: =SUMSQ(B2:B7). We ran it in Google Sheets mode on the same data and it returns 44.82.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(B2:B7^2) | 44.82 | The same result; handy when you need to square something calculated, like a difference. |
| =SUMPRODUCT(B2:B7,B2:B7) | 44.82 | Multiplies the range by itself; works in every version. |
Common mistakes
=SUM(B2:B7)^2squares the total, which is a different number (256 here, not 44.82).- In statistics, "sum of squares" often means squared deviations from the mean, not of the raw values. See the first FAQ.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate the sum of squares
E1, calculate the sum of the squares of the readings in B2:B7.Questions
How do I calculate the sum of squared deviations?
Use =SUMPRODUCT((B2:B7-AVERAGE(B2:B7))^2), or the built-in DEVSQ: =DEVSQ(B2:B7).
How do I sum the squared differences between two columns?
Use =SUMPRODUCT((B2:B7-C2:C7)^2).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.