Excel SUMSQ: How to calculate the sum of squares

Use SUMSQ in Excel to square each number and add the results. For 1, 2, 3, and 4, the result is 30 from 1²+2²+3²+4². Adding the numbers first and squaring the total gives 100, so the order of operations matters.

Enter numbers directly or refer to a range

Enter 1, 2, 3, and 4 in A2:D2. The first formula passes the numbers directly. The second refers to the cells. Enter each formula in a separate blank cell and both return 30.

=SUMSQ(1,2,3,4)
=SUMSQ(A2:D2)

The first argument is required, and an Excel worksheet formula accepts up to 255 arguments. A range such as A2:D2 counts as one argument. Use a cell reference if you want the result to recalculate when the input changes.

Microsoft’s SUMSQ syntax and argument reference

Compare a sum of squares, a squared total, and squared deviations

Enter -2, 2, and 3 in A5:A7. Put each formula in a separate blank cell. They all use the same input range.

Formula Result Order of operations
=SUMSQ(A5:A7) 17 Square each value and add the squares
=SUM(A5:A7)^2 9 Add the values and square the total
=DEVSQ(A5:A7) 14 Square each difference from the mean of 1 and add the results

For example, if three differences from a target are -2, 2, and 3, SUMSQ gives 17 as the total of the squared differences. If you want the spread around their own mean of 1, DEVSQ gives 14.

Microsoft’s DEVSQ reference for squared deviations

When a cell contains a number stored as text

Enter the number 2 in A9. In A10, type an apostrophe followed by 3 so that the cell stores text. Leave A11 empty and enter 0 in A12. The first formula reads the range and returns 4 from the numeric values 2 and 0. The second passes the numeric text 3 directly as an argument and returns 13.

=SUMSQ(A9:A12)
=SUMSQ(2,"3")

In a referenced range, blanks, text, and logical values do not contribute to SUMSQ. If the result is smaller than expected, check whether any values you want to count are stored as text.