SUMSQ function
SUMSQ squares each value and adds the squares together. It is a step in variance and distance calculations, and text in ranges is ignored by the function.
Example
Read it as a sentence: square each value in A1:A5 and add up the squares, which gives 55.
Excel shows 55
A
1
1
2
2
3
3
4
4
5
5
Data
Syntax
SUMSQ(number1, [number2], ...)
Arguments in [square brackets] are optional.
-
number1(required) - The first number or range.
-
[number2], ...(optional) - More numbers or ranges.
Returns: The sum of the squares of the inputs.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SUMSQ(1,2,3) | 14 | Scalars |
=SUMSQ({1,2,3}) | 14 | Array constant |
=SUMSQ("2",3) | 13 | Text number arg counted |
=SUMSQ(TRUE,3) | 10 | TRUE arg counted |
=SUMSQ({1,"2",TRUE}) | 1 | Array with text |
=SUMSQ(Data!A1:A5) | 55 | Range |
=SUMSQ("a",3) | #VALUE! | Text arg |
=SUMSQ(Data!C1:C5) | #N/A | Error in range |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
C
1
1
1
2
2
2
3
3
=NA()
4
4
4
5
5
5
Data
Watch out for
- Text in ranges is ignored.
Where it works
- Excel
- SUMSQ is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .