Exceljet

Quick, clean, and to the point

Excel SUMX2PY2 Function

Excel SUMX2PY2 function
Summary 

The Excel SUMX2PY2 function returns the sum of the sum of squares of corresponding values in two arrays. Values can be supplied as constants, cell references, or ranges.

Purpose 
Get sum of squares in two arrays
Return value 
Calculated sum of sum of squares
Syntax 
=SUMX2PY2 (array_x, array_y)
Arguments 
  • array_x - The first range or array containing numeric values.
  • array_y - The second range or array containing numeric values.
Version 
Usage notes 

The SUMX2PY2 function returns the sum of the sum of squares of corresponding values in two arrays. The "p" in the function name stands for "plus", as in "sum x2 plus y2". SUMX2PY2 can accept values as constants, references or ranges.

Examples

=SUMX2PY2({1,2},{1,2}) // returns 10
=SUMX2PY2({1,2,3},{1,2,3}) // returns 28

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

=SUMX2PY2(B5:B12,C5:C12)

which returns 600 as a result.

Equation

The equation used to calculate the sum of 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 SUMX2PY2:

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

Notes

  • Arguments can any mix of constants, names, arrays, or references that contain numbers.
  • Empty cells are ignored, but cells with zero values are included.
  • SUMX2PY2 returns #N/A if the arrays contain different numbers of cells.
  • If a cell in array_x or array_y is empty, or contains text, the pair of values are ignored.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.