SUMSQ function

The SUMSQ function in Excel is used to calculate the sum of the squares of a range of numbers. Essentially, it squares each number in the specified range and then adds up all the squared values. This function is useful in statistical analysis, such as calculating the variance or standard deviation, as squaring values is a key part of these calculations.

Syntax

=SUMSQ(number1, [number2], ...)

Parameters

• number1 (required): The first number or range of numbers to square and add.
• [number2, …] (optional): Additional numbers or ranges to square and add. You can specify up to 255 arguments.

Key Points

1. Squaring: The SUMSQ function squares each number in the provided range or argument.
2. Multiple Arguments: You can include multiple ranges or values. Excel will square each number in all the specified ranges or arguments and sum the results.
3. Ignore Text: Non-numeric values (including text and empty cells) are ignored in the calculation.

Examples

1. Sum of squares for a range (A1:A5):

   =SUMSQ(A1:A5)
   

   Result: Squares each value in A1:A5 and adds them together. For example, if A1:A5 contains the values 1, 2, 3, 4, and 5, the result would be:

   $$1^2 + 2^2 + 3^2 + 4^2 + 5^2 = 1 + 4 + 9 + 16 + 25 = 55$$

2. Sum of squares for individual numbers:

   =SUMSQ(2, 3, 4)
   

   Result: Squares each number and sums them.

   $$2^2 + 3^2 + 4^2 = 4 + 9 + 16 = 29$$

3. Sum of squares for multiple ranges (A1:A3 and B1:B3):

   =SUMSQ(A1:A3, B1:B3)
   

   Result: Squares each value in both A1:A3 and B1:B3, and sums the results.

4. Sum of squares with mixed ranges and values:

   =SUMSQ(1, 2, 3, A1:A5)
   

   Result: Squares the numbers 1, 2, and 3, then squares each value in the range A1:A5 and sums all the results.

Notes

• Text and Non-Numeric Data: If the range contains text or non-numeric values, they are ignored.
• Negative Numbers: The function squares negative numbers as well, turning them into positive values. For example, SUMSQ(-2, 3) would give (-2)^2 + 3^2 = 4 + 9 = 13.

Related Functions

• SUM: Adds up a range of numbers without squaring them.
• SUMPRODUCT: Similar to SUMSQ, but for multiplying corresponding values and then summing the products.
• VAR / VAR.P / VAR.S: These functions are used to calculate variance, which involves squaring deviations from the mean, similar to SUMSQ.
• STDEV.P / STDEV.S: These functions calculate standard deviation, which is based on variance and also involves squaring differences from the mean.

The SUMSQ function is a simple yet effective tool for statistical and mathematical operations in Excel, particularly when you need to square and sum values quickly.