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.


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:


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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.