Purpose

Return value

Syntax

=SUMSQ(number1,[number2],...)
  • number1 - The first argument containing numeric values.
  • number2 - [optional] The first argument containing numeric values.

Using the SUMSQ function

The SUMSQ function returns the sum of the squares of the numbers provided. SUMSQ takes multiple arguments in the form number1 , number2 , number3 , etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range . All numbers in the arguments provided are squared then summed. The SUMSQ function automatically ignores empty cells and text values.

Examples

=SUMSQ(1,2) // returns 5
=SUMSQ(1,2,3) // returns 14
=SUMSQ({1,2,3}) // returns 14

In the worksheet shown, the formula in H5, copied down, is:

=SUMSQ(B5:F5) // returns 5

Notice that SUMSQ automatically ignores empty cells and text values.

Notes

  • Arguments can be a mix of constants, names, arrays, or references that contain numbers.
  • Empty cells, logical values, and text values are ignored when they appear in arrays or references.
  • The logical values TRUE and FALSE are evaluated as 1 and 0 respectively, but only when they are hardcoded as arguments.
  • Numbers entered as text (i.e. “1”, “3”, etc.) are evaluated, but only when they are hardcoded as arguments.

Purpose

Return value

Syntax

=SUMX2MY2(array_x,array_y)
  • array_x - The first range or array containing numeric values.
  • array_y - The second range or array containing numeric values.

Using the SUMX2MY2 function

The SUMX2MY2 function returns the sum of the difference of squares of corresponding values in two arrays. The “m” in the function name stands for “minus”, as in “sum x 2 minus y 2 “.

SUMX2MY2 takes two arguments , array_x and array_y . Array_x is the first range or array or range of numbers, and array_y is the second range or array of numbers. Both arguments can be provided as an array constant or as a range .

Examples

=SUMX2MY2({0,1},{1,2}) // returns -4
=SUMX2MY2({1,2,3},{1,2,3}) // returns 0

In the example shown above, the formula in E5 is:

=SUMX2MY2(B5:B12,C5:C12)

which returns -144 as a result.

Equation

The equation used to calculate the sum of squares is:

This formula can be created manually in Excel with the exponentiation operator (^) like this:

=SUM((range1^2)-(range2^2))

With the example as shown, the formula below will return the same result as SUMX2MY2:

=SUM((B5:B12^2)+(C5:C12^2)) // returns -144

Notes

  • Arguments can be a mix of constants, names, arrays, or references that contain numbers.
  • Text values are ignored, but cells with zero values are included.
  • SUMX2MY2 returns #N/A if the arrays contain different numbers of cells.