Quick, clean, and to the point

Excel SUMX2MY2 Function

Excel SUMX2MY2 function

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

Sum of difference of squares in two arrays
Return value 
Calculated sum of difference of squares
=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.
Usage notes 

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 x2 minus y2". 

SUMX2MY2 takes two arguments, array_x and array_yArray_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.


=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:


which returns -144 as a result.


The equation used to calculate the sum of squares is:


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


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

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


  • 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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas