SUMX2PY2 in Excel: Add Two Squared Totals and Audit the Meaning
Use Excel SUMX2PY2 with complete numeric arrays, geometric interpretations, cross-term checks and documented length errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: SUMX2PY2 adds x²+y² for each corresponding pair. With x values 3,4,0 and y values 1,2,5, the expected result is 55, the sum of squared totals 25 and 30. It does not square x+y or measure disagreement between arrays.
Availability and version history
- Introduced: first introduction version is not established by the official history reviewed; the current product list is not a launch record.
- Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
- Checked: October 14, 2026; official SUMX2PY2 reference. Documentation and independently calculated examples; no native Excel execution or broader platform certification.
Syntax and input scope
=SUMX2PY2(array_x, array_y)
Both arguments are required. Microsoft documents #N/A for different numbers of values. Referenced text, logical values and empty cells are ignored, while numeric zeros count under its stated rule.
These examples deliberately use equal-length, all-numeric columns. The reference’s compact ignore statement does not define a complete mixed-type pairing matrix, so native handling of mismatched blank/text positions is not certified here.
Example 1: list every squared contribution
Enter A2:B4:
| A · x | B · y |
|---|---|
| 3 | 1 |
| 4 | 2 |
| 0 | 5 |
In C2 enter =A2^2+B2^2 and copy down. Expected contributions are 10,20,25:
=SUMX2PY2(A2:A4,B2:B4)
=SUM(C2:C4)
Both expected results are 55. C4 includes the numeric zero’s square and the y value’s square; it is not a row to discard simply because x is zero.
The output is one total. It does not return the individual contributions automatically, which is why the helper column is useful for checking each row.
Example 2: distinguish a square of a sum
In D2 enter =(A2+B2)^2 and fill through D4. Expected values are 16,36,25, totaling 77.
The difference from 55 is 22, twice the paired cross-product total 3×1+4×2+0×5=11. Algebraically, (x+y)² contains a 2xy term that x²+y² does not.
This distinction is especially important when translating a mathematical expression into a similarly named spreadsheet function. Keep the parentheses visible in a helper calculation.
Advanced example: check the independent squared totals
=SUMSQ(A2:A4)+SUMSQ(B2:B4)
Expected result: 55, from 25+30. This is an independent all-numeric identity using SUMSQ.
For complete numeric arrays, reordering either array does not change the two squared totals. Consequently, an unchanged SUMX2PY2 result is not proof that corresponding records remained aligned. Equal dimensions are necessary for the documented call but do not establish correct record identity.
Advanced example: interpret squared distance from an origin
Treat each row as a point (x,y). The squared distances from the origin are 10,20,25, with distances approximately 3.162277660, 4.472135955 and 5.
The function adds the squared distances, producing 55. Taking one square root afterward, =SQRT(SUMX2PY2(A2:A4,B2:B4)), gives approximately 7.416198487. That is the norm of all six coordinates taken together, not the sum of the three point-to-origin distances.
The latter sum is approximately 12.634413615, calculated by taking each row’s square root first and then adding. These are different geometric summaries.
Advanced check: it is not an agreement score
=SUMX2PY2(A2:A4,A2:A4)
=SUMXMY2(A2:A4,A2:A4)
Expected results are 50 and 0. The first counts the same squared values twice; the second compares identical arrays and has zero squared difference.
If a task asks whether predictions match observations, SUMXMY2 may express that squared-error question. A combined magnitude of 50 says nothing by itself about agreement.
Scaling, signs and origin
Negating either whole array leaves SUMX2PY2 unchanged, because each entry is squared. Doubling every entry in both arrays gives 220, four times the original 55.
Adding ten to every entry instead produces squared totals 465 and 490, for a combined result 955. The function measures magnitude relative to zero, so changing the origin affects its value. Keep the units and reference point explicit in a geometric or statistical model.
Errors and boundaries
Using A2:A4 with B2:B3 supplies different numeric lengths and has Microsoft’s documented expected #N/A. Empty/text conversion, error-valued entries and nonmatching two-dimensional shapes need their own supported controls; the successful column examples do not establish them.
A sum of real squares is nonnegative in exact mathematics. Native extreme-magnitude overflow and last-digit precision are not measured here. For subtracting squared totals instead, consult SUMX2MY2.
References and verification
Inputs are fictional. Decimal answers are independent mathematical expectations rounded to nine places, not captured native results. Extreme magnitudes, complete coercion matrices and cross-version numerical accuracy remain untested.