How to Use SUMXMY2 Function in Google Sheets

The SUMXMY2 function in Google Sheets is a mathematical function designed specifically to return the sum of squares of differences of corresponding values in two arrays.

In other words, if we have two equal-sized arrays with this function, we can know the sum of the squares of the differences between each two opposite elements in both arrays.

So, what’s an array?

An array in Google Sheets is a series of populated cells, whether horizontal or vertical, which means  either one row of cells or one column of cells.

Now, consider that we have an array named ‘D’ with the following three numbers (1, 4, 9), and an array named ‘O’ with these numbers (8, 7, 4). Now, if we want the sum of squared differences of these two arrays, we use the SUMXMY2 function.

So, what would be the calculation equation for the previous example? Let’s see.

Here’s the general equation for the sum of squared differences:

Now, if you apply this formula to our data for arrays ‘D’ and ‘O’, we get the following equation:

Sum of sums of squares = (8 – 1)² + (7 – 4)² + (9 – 4)² = 7² + 3² + 5² = 83

And here’s the SUMXMY2 function getting the same result in Google Sheets.

I’m sure the SUMXMY2 function is a bit clearer now, but let’s go ahead and learn more about it in the next section.

The Anatomy of the SUMXMY2 Function in Google Sheets

The SUMXMY2 function shall be written as follows:

= SUMXMY2(array_x, array_y)

Let’s dissect this thing and understand what each of these terms means:

• = (the equal sign) is just how we start any function in Google Sheets.
• SUMX2PY2 is the name of the function we are using.
• () These parentheses are used to host the two values we put in our function, and a comma “,” must separate these values.

Note that the values hosted in any google sheets function are called arguments.

• (array_x) 1st argument. This argument takes a series of cells in either one row or one column.
• (array_y) 2nd argument. Same as the argument before.

Now, let’s take a moment here before we dive deeper into this function. There are some things to be aware of. Or else, your results won’t be accurate.

• If any value in any of the two arrays selected in the function were a text, this value would equal to 0.
• If you put a date in any of the arrays in the function, this date will be considered the date value, which will mess up your calculations.
• The two arrays you put into the SUMXMY2 function must be of the same size. Otherwise, an error will occur.

Now for further explanation, let’s go through the SUMXMY2 function together, and you will understand it once you start practicing its application.

A Real Example of Using SUMXMY2 Function

Take a look at the example below to see how the SUMXMY2 function is used in Google Sheets.
Let’s say we have the two following arrays.

• Array M: 1, 5, 8, 19, 16, 6, 61, 2, 14, 23, 14, 89, 16
• Array Q: 14, 91, 7, 64, 33, 2, 31, 18, 11, 4, 5, 19, 87

And that’s how we input our data into Google Sheets:

Now tell me, at first glance do you notice anything that might cause a faulty result?

Well, it’s the value in cell B6, being left aligned like that should indicate that it’s a text, which means it will be equal to 0. So, it needs fixing to avoid any mistakes.

Let’s see how to fix it in two steps:

1. First, let’s look at its current format.
2. Then we change its format from text to ‘Number’ or ‘Automatic’.You can see now that it’s fixed, as it became right aligned.
3. We can now remove the decimal points to make our value consistent with the rest of the values.Click on ‘Decrease decimal places’ twice to remove the two extra zeros.

Now that we have prepared our two arrays, let’s calculate their ‘sum of sums of squares’ using the SUMXMY2 Function of Google Sheets.

You may make a copy of the spreadsheet using the link attached below.

Make a copy of example spreadsheet

How to Use the SUMXMY2 Function in Google Sheets

1. Simply click on any cell to make it the active cell. For this guide, I will be selecting C2, where I want to show my formula. Then type ‘=‘.
2. Now, type ‘SUMX‘ and click on the SUMXMY2 function or press Tab to select it.
   Notice that there are three options to select from. We are only concerned with the first one, the SUMXMY2 function, so don’t confuse it with the other ones, SUMX2MY2 and SUMX2PY2, as these functions are different.
3. Now, fill in the function’s first argument, array_x, which would be the range in cells A2:A14. So, you can select it or simply just write ‘A2:A14’.
4. Then, type comma ‘,‘.
5. Now, go ahead and select the second argument; array_y, which is the value in B2:B14.
6. Now, close the parenthesis, press Enter, and the result will take over.

That’s pretty much it. Congrats! You now learned the SUMXMY2 function in Google Sheets.

Are you looking forward to learning some more amazing Google Sheets functions? Well, you are in luck, as you are a single click away from your destination.

Get emails from us about Google Sheets.

Subscribe