SUMX2MY2 in Google Sheets: Subtract Two Squared Totals
Use Sheets SUMX2MY2 with a fully checked paired table, negative contributions, cancellation and contrasts with squared differences.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: SUMX2MY2 adds each x²−y² contribution. For x values 2,5,−1 and y values 1,−2,3, the squared totals are 30 and 14, so the independently expected result is 16. This is not the sum of (x−y)².
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 SUMX2MY2 reference. Documentation and independently calculated examples, without native Sheets execution.
Syntax and paired inputs
=SUMX2MY2(array_x, array_y)
Both arrays or ranges are required. Google describes subtracting each corresponding y square from the x square and adding the results. The worked inputs have identical shapes, complete numeric pairs and deliberately aligned rows.
The output is one number, not a per-row array. Use English function names and comma separators. The short reference does not establish a complete mismatched-length, mixed-type or native error-code table; those cases remain unverified rather than inheriting another program’s rules.
Example 1: build and inspect the paired table
Enter this data in A1:B4:
| Row | A · x | B · y |
|---|---|---|
| 1 | x | y |
| 2 | 2 | 1 |
| 3 | 5 | -2 |
| 4 | -1 | 3 |
=SUMX2MY2(A2:A4,B2:B4)
Expected result: 16. The three contributions are 3,21 and −8, because 4−1=3, 25−4=21 and 1−9=−8. Negative input values are squared before subtraction, so the last contribution’s sign depends on the squared magnitudes rather than simply on x being negative.
Example 2: verify with explicit helper formulas
In C2 enter and fill through C4:
=A2^2-B2^2
The helper column should show the expected contributions 3,21,−8. =SUM(C2:C4) has expected result 16. A second independent check is:
=SUMSQ(A2:A4)-SUMSQ(B2:B4)
Its expected result is also 16, from 30−14. This identity is valid for the complete numeric data shown. It is not a guarantee that every function in the expression handles missing or text-valued cells identically.
Advanced example: distinguish squared differences
=SUMXMY2(A2:A4,B2:B4)
Expected result: 66, from (2−1)²+(5−(−2))²+(−1−3)²=1+49+16. Squaring after subtraction creates cross terms and loses the sign of each difference.
SUMXMY2 therefore answers a different question. A positive SUMX2MY2 result means the first squared total is larger for these inputs; it does not measure total point-by-point mismatch.
Advanced check: zero can hide unequal observations
On a separate sheet, use x values 3,4 and y values 0,5:
=SUMX2MY2(A2:A3,B2:B3)
Both squared totals equal 25, so the expected result is 0. Yet the paired observations are not identical: the sum of squared differences is 9+1=10.
Do not use a zero difference of squared totals as an equality test for two datasets. Positive and negative contributions can cancel, leaving a scalar that hides substantial row-level differences.
Advanced example: reverse arguments and change signs
For the original three-row table:
=SUMX2MY2(B2:B4,A2:A4)
Expected result: −16, because reversing the two squared totals reverses their difference. If every x and y input is negated instead, the result remains 16 because each square is unchanged.
A useful cross-check is that a dataset compared with itself has expected result zero. These algebraic controls test the intended operation but cannot reveal whether two same-sized ranges were accidentally taken from different records.
Advanced example: distinguish scaling from translation
Doubling every original x and y value multiplies each squared contribution by four, producing expected result 64.
Adding 10 to both columns creates x values 12,15,9 and y values 11,8,13. Their squared totals are 450 and 354, so the expected difference is 96, not 16. Translating both datasets by the same constant does not preserve a difference of squared totals.
This differs from a sum of squared paired differences, where adding the same constant to both sides cancels before squaring. Record transformations explicitly when comparing reports.
Data quality and native boundaries
The source defines correspondence between the arrays but does not specify every rule for skipping text or blank cells. Keep complete pairs, preserve row identifiers and do not assume that independently removing missing entries from each column retains alignment.
The direct Google reference was reviewed. No native mismatch error code, empty-array result, maximum size or extreme-precision behavior is certified. For the sum rather than difference of the squared totals, see SUMX2PY2.
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.