SUMXMY2 in Google Sheets: Sum Squared Paired Errors
Measure Sheets paired squared differences with row-alignment checks, a complete prediction-error example and contrasts with signed totals.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: SUMXMY2 subtracts corresponding values, squares each difference and totals those squares. For x values 2,5,−1 and y values 1,−2,3, the differences are 1,7,−4 and the independently expected result is 66.
Availability and version history
- Introduced: first service launch date is not established by the official history reviewed; no numbered desktop introduction is assigned.
- Availability: documented in the rolling Google Sheets service. Examples target ordinary web-spreadsheet cells; mobile clients and imported file modes are not separately certified.
- Checked: October 14, 2026; official SUMXMY2 reference. Documentation and independently calculated examples, without native Sheets execution.
Syntax and why alignment matters
=SUMXMY2(array_x, array_y)
Both arrays or ranges are required. Google explicitly describes subtracting corresponding y entries from x entries before squaring. These examples use complete same-shaped numeric columns representing paired records.
The output is one squared-error total. It is not the square of the total signed error. Neither is it the difference between the two raw squared totals. English function names and comma separators are used below.
Example 1: inspect every difference
Enter A1:B4 as follows:
| Row | A · x | B · y |
|---|---|---|
| 1 | x | y |
| 2 | 2 | 1 |
| 3 | 5 | -2 |
| 4 | -1 | 3 |
=SUMXMY2(A2:A4,B2:B4)
Expected result: 66, from 1²+7²+(−4)²=1+49+16. In C2 enter =A2-B2 and fill down to see 1,7,−4. In D2 enter =C2^2 and fill down to see 1,49,16; their sum verifies the result.
Keep the range rows aligned to the same source records. A correct formula cannot infer that one column was independently sorted or filtered into another order.
Example 2: distinguish signed total and squared total
The signed differences sum to 4, whose square is 16. That is different from the expected sum of squares 66.
Positive and negative errors can cancel before a total is squared. Squaring each error first prevents that cancellation, while also giving larger errors more influence. State which quantity the report needs rather than using the two expressions interchangeably.
For these data, SUMX2MY2 has expected result 16 too, but that agreement is coincidental: its definition is x-square total 30 minus y-square total 14.
Advanced example: demonstrate the effect of row order
Keep x as 2,5,−1, but reorder y to 3,1,−2 in a separate column. Paired differences become −1,4,1 and the sum of their squares is 18.
The reordered y column has the same individual values and squared total as before, yet the paired discrepancy changes from 66 to 18. Row correspondence is part of the input meaning, not merely a presentation detail.
Keep record identifiers with both measured and expected values. If data comes from separate sources, join on a verified key before evaluating paired errors. Equal range lengths alone do not prove the rows represent the same cases.
Advanced example: calculate a prediction-error summary
On a separate sheet, enter observations 10,12,8 in A2:A4 and predictions 9,14,8 in B2:B4:
=SUMXMY2(A2:A4,B2:B4)
Expected squared-error sum: 5, from errors 1,−2,0 and squares 1,4,0. For these three complete pairs:
=SUMXMY2(A2:A4,B2:B4)/3
=SQRT(SUMXMY2(A2:A4,B2:B4)/3)
Expected mean squared error is 1.666666667, and root mean squared error is 1.290994449, approximately. The square root restores the original measurement unit.
The denominator three is the explicitly stated count of complete paired records. It is not an automatic residual-degrees-of-freedom correction for a fitted statistical model. If model fitting changes the intended denominator, document that separately.
Advanced check: reverse arguments and compare identical arrays
For the original x and y columns:
=SUMXMY2(B2:B4,A2:A4)
=SUMXMY2(A2:A4,A2:A4)
Expected results are 66 and 0. Reversing subtraction changes every error’s sign, but the squares stay the same. An array compared with itself has zero difference at every position.
These controls are useful for catching an unintended change in operation. A low aggregate still does not reveal every individual large error, so retain helper residuals when diagnosing data quality.
Advanced example: translation and scale controls
Add 10 to every x and y value in the original table. Each paired difference is unchanged, so the expected result remains 66. This contrasts with sums of raw squared magnitudes, which change when the origin changes.
Multiply all original x and y values by two instead. The differences double and their squares quadruple, giving expected result 264. A measurement-unit change must therefore be reflected in the squared unit reported beside the output.
An independent identity gives the same original total: x-square sum 30 plus y-square sum 14 minus twice the cross-product sum −11 equals 66. The helper residual method remains clearer for verifying individual records.
Source and error boundaries
Google’s indexed official help supplied the definition and syntax; a later direct refresh was rate-limited and was not retried. No native Sheets output or complete mismatch/error matrix was captured.
Do not assume a rule for skipping text, empty cells or errors based only on the shared function name in another spreadsheet. Keep complete numeric pairs for these controls and separately validate the target file’s required edge cases. SUMX2PY2 instead adds raw squared magnitudes and does not measure paired error.
References and verification
Inputs are fictional. Approximate answers are independent mathematical expectations rounded to nine places, not captured native results. Complete type/error matrices, extreme-magnitude accuracy and cross-client behavior remain untested.