Check Sheet Logo
Check Sheet


The SUMXMY2 formula returns the sum of the squared differences between two arrays of numbers. It is commonly used in statistical analysis to measure the degree of association between two sets of variables.


Use the SUMXMY2 formula with the syntax shown below, it has 2 required parameters:

=SUMXMY2(array_x, array_y)
  1. array_x (required):
    The first array of numbers to compare.
  2. array_y (required):
    The second array of numbers to compare.


Here are a few example use cases that explain how to use the SUMXMY2 formula in Google Sheets.

Calculate the variance of a set of data

By using SUMXMY2 to calculate the squared differences between each value in a data set and its mean, you can then divide the result by the number of observations to obtain the variance.

Find the sum of squares regression

In regression analysis, you can use SUMXMY2 to find the sum of squares regression, which represents the variation in the dependent variable that is explained by the independent variable.

Calculate the coefficient of determination

The coefficient of determination (R-squared) is a measure of how well the regression line fits the observed data. SUMXMY2 can be used to calculate the sum of squares residual, which is then used to calculate R-squared.

Common Mistakes

SUMXMY2 not working? Here are some common mistakes people make when using the SUMXMY2 Google Sheets Formula:

Not Providing Two Arrays

The SUMXMY2 function requires two arrays to be provided as input. If only one array is provided, or if more than two arrays are provided, the function will return an error.

Arrays with Different Sizes

The two arrays provided to the SUMXMY2 function must be of equal size. If they are not, the function will return an error.

The following functions are similar to SUMXMY2 or are often used with it in a formula:


    The SUMSQ formula is a Math formula that returns the sum of the squares of a given set of numbers. It is commonly used to calculate the sum of squares for a set of values, which can be useful in statistical analysis and other mathematical operations.


    The CORREL formula returns the correlation coefficient between two sets of data. This coefficient represents the strength of the linear relationship between the two sets of data, with values ranging from -1 (perfect negative correlation) to 1 (perfect positive correlation).

Learn More

You can learn more about the SUMXMY2 Google Sheets function on Google Support.