# How to calculate the sum of squares in Excel

Use SUMSQ: `=SUMSQ(B2:B7)` squares each value and adds the squares.

**Excel:** `=SUMSQ(B2:B7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Sample | Reading |  | Sum of squares |
| S1 | 2.4 |  |  |
| S2 | 3.1 |  |  |
| S3 | 1.8 |  |  |
| S4 | 2.9 |  |  |
| S5 | 3.6 |  |  |
| S6 | 2.2 |  |  |

Formula in E1: `=SUMSQ(B2:B7)`

Result: 44.82

## 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.

## Other ways

- `=SUMPRODUCT(B2:B7^2)`: The same result; handy when you need to square something calculated, like a difference.
- `=SUMPRODUCT(B2:B7,B2:B7)`: Multiplies the range by itself; works in every version.

## Common mistakes

- `=SUM(B2:B7)^2` squares 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-of-squares