SUMX2PY2 Function (LibreOffice Calc)
Learn the SUMX2PY2 function in LibreOffice Calc: Returns the total of the square sum of two arrays.
Verification status: Selected examples were executed in LibreOffice Calc 25.2.3.2. This does not establish that every formula and edge case was tested. How formula examples are checked
Quick answer: SUMX2PY2 adds X² + Y² across matching pairs. In the example sheet, =SUMX2PY2(A2:A6;B2:B6) returns 594, the sum of 285 squared X units and 309 squared Y units. It does not square the pairwise sums (X+Y); it adds squares of each input independently. [LO] [NATIVE]
Use it when you need a combined squared magnitude across two aligned numeric series, a building block in certain vector calculations and sum-of-squares diagnostics. For real numeric inputs, the result cannot be negative. It is not a sum of differences and should not be used as an error metric without transforming the data appropriately.
Exact Calc syntax, required arguments and locale
=SUMX2PY2(ArrayX;ArrayY)
ArrayX — Required. A cell range or numeric expression; a single number also worked in native tests The first array, in X order.
ArrayY — Required. A correspondingly shaped range or numeric expression; a single number also worked in native tests The second array, in Y order.
Both arguments are required: no default is documented. In the commonly used English Calc formula configuration, arguments are separated with semicolons (;); some locale/software configurations display different separators or localized function names. The English examples here are native-tested using semicolons. The tested .ods file serialized this function using the of:=SUMX2PY2 OpenFormula form with cell references such as [.A2:.A6]; that is an internal file representation, not a formula-bar prefix to type. Do not infer a different supported alias or a minimum release version from the stored ODF text. Official Calc Help describes arrays rather than enumerating every scalar/text case; those edge cases below are identified explicitly as observed behavior in Calc 25.2.3.2. [LO]
Reproducible practice sheet: two columns of matched numbers
Enter the following values into a fresh Calc worksheet. The actual column has deliberately been made larger than the expected column for two rows so that the three similarly named functions produce different results.
On a narrow screen, scroll this table sideways to see every column.
| Row | A — Expected | B — Actual | A² | B² | (A−B)² |
|---|---|---|---|---|---|
| 1 | Expected | Actual | — | — | — |
| 2 | 3 | 2 | 9 | 4 | 1 |
| 3 | 5 | 5 | 25 | 25 | 0 |
| 4 | 7 | 6 | 49 | 36 | 1 |
| 5 | 9 | 10 | 81 | 100 | 1 |
| 6 | 11 | 12 | 121 | 144 | 1 |
| Sum | 35 | 35 | 285 | 309 | 4 |
The A and B cells are the only required inputs; the other columns in this table are hand-calculated explanations, not dependencies. Save the practice workbook in standard .ods format if you want to reproduce the documented OpenFormula serialization tests.
Worked example: combined sum of squares
=SUMX2PY2(A2:A6;B2:B6)
Native result 594. The row contributions are 3²+2² = 13, 5²+5² = 50, 7²+6² = 85, 9²+10² = 181, and 11²+12² = 265. Thus 13 + 50 + 85 + 181 + 265 = 594. The independent arithmetic and native Calc result agree. [NATIVE]
Advanced examples: identities, normalization and signed numbers
Equivalent check with SUMSQ. For this complete numeric dataset, both formulas produce 594:
=SUMX2PY2(A2:A6;B2:B6)
=SUMSQ(A2:A6;B2:B6)
The first explicitly pairs two equal-shaped input arrays; the second totals the squares of the values passed to SUMSQ. The native test confirmed this identity on the practice dataset. Avoid generalizing the equivalence to all possible combinations of text, blank cells and mismatched ranges without separate testing. [NATIVE]
Treat negative values correctly. With X=−3, −2, 0, 2, 3 in A10:A14, Y=−2, −2, 0, 2, 2 in B10:B14, =SUMX2PY2(A10:A14;B10:B14) returns 42, not −42. A negative input is squared before summation. [NATIVE]
Symmetric input order. =SUMX2PY2(B2:B6;A2:A6) also gives 594. =SUMX2PY2(A2:A6;A2:A6) returns 570 because the squared total 285 is counted twice. =SUMX2PY2(5;3) returns 34. These were native assertions. [NATIVE]
From sum of squares to Euclidean magnitude. For two compatible lists interpreted as a ten-element vector, the expression =SQRT(SUMX2PY2(A2:A6;B2:B6)) represents the square root of the combined sum of squares, √594. This is a mathematical consequence of the tested result, not an independently executed assertion of the exact composite formula in the recorded checks. The presence of a square root does not make the untransformed function a standard deviation or error statistic.
Do not confuse with squared sums. SUMX2PY2 computes Σ(x² + y²), while Σ(x+y)² contains an extra 2xy term. For the first row, 13 (3² + 2²) differs from 25 ((3+2)²).
Troubleshooting, errors and data-quality checks
These error strings and codes are observed in LibreOffice Calc 25.2.3.2, not promises for all future versions, locales or other spreadsheet programs.
On a narrow screen, scroll this table sideways to see every column.
| Symptom | Likely cause in the tested sheet | What to check |
|---|---|---|
#VALUE! (native code 519) with unequal ranges |
One argument has five rows and the other has four; or equal counts arranged in different row/column shapes | Confirm the same shape and alignment, e.g. A2:A6 alongside B2:B6, not B2:B5 or a horizontal row. |
Err:511 (code 511) |
Both mandatory arguments omitted | Enter both ranges/numbers, separated correctly. |
#DIV/0! (code 532) |
An input cell evaluates to division by zero | Repair the underlying source formula before invoking SUMX2PY2. |
Surprising answer, 0 or #VALUE! |
Imported numbers stored as text, empty cells, Boolean cells or differently shaped ranges | Test with ISNUMBER, inspect individual input pairs, and clean/convert input cells rather than assuming text is numeric. |
Important text-data difference: With two numeric columns interrupted by text entries, the three related functions did not respond identically in the native observations. SUMX2PY2(D2:D6;E2:E6) yielded 19 with text-interrupted pairs; SUMXMY2 yielded #VALUE! on that same sheet. Those observations are not generalized to every version or imported workbook. A safer template is to require a complete, numeric pair in every included row; do not rely on an undocumented coercion rule. [NATIVE]
LibreOffice, Excel, Sheets and ODF compatibility
LibreOffice Calc 25.2.3.2 natively executed the examples above. LibreOffice 26.8 Help describes the same SUMX2PY2 operation and specifies two arrays. [LO] Microsoft Excel and Google Sheets each document a function with the same English name and two arguments; their displayed comma separators are examples of vendor/locale syntax, not a reason to paste commas unchanged into this Calc sheet. [MS] [GS]
Do not carry LibreOffice’s observed error codes or mixed-text handling into Excel or Sheets without testing there. Microsoft documents #N/A for arrays with different numbers of elements, while our native Calc tests showed #VALUE! / code 519 for unequal ranges. Google Sheets was documentation-checked only, as was Excel. The written mathematics agrees; platform-specific coercion, error messages, and locale delimiters can differ. Existing LibreOffice source metadata does not establish an independently verified support matrix across older OpenOffice releases or mobile editions.
Related, checked LibreOffice tutorials
- SUMPRODUCT in LibreOffice Calc — sums corresponding products; use it with
SUMPRODUCT((A2:A6-B2:B6)*(A2:A6-B2:B6))for a direct squared-difference expression. - SUM in LibreOffice Calc — adds ordinary numbers or ranges without any pairwise squaring.
Verified sources, execution evidence and editorial notes
- [LO] LibreOffice 26.8 Help — Array Functions, section “SUMX2PY2”; checked 2026-10-11. https://help.libreoffice.org/latest/en-US/text/scalc/01/04060107.html — authoritative definition, argument order and two-array syntax.
- [MS] Microsoft Excel Support — SUMX2PY2 function; checked 2026-10-11. https://support.microsoft.com/en-us/excel/functions/sumx2py2-function — comparable Excel definition and documented Excel error behavior; not natively tested here.
- [GS] Google Sheets Help — SUMX2PY2; checked 2026-10-11. https://support.google.com/docs/answer/3094260?hl=en — comparable two-array definition; not natively tested here.
- [NATIVE]
native-results.json+run_native.py+SUMX_native_tests.odsin this immutable review batch. The script declares expected arithmetic independently, executes it through an isolated headless LibreOffice UNO session, and records actual values/error codes. 29 of 29 assertions passed across all three functions; 18 additional inputs were observed but not treated as pass/fail assertions. Access date: 2026-10-11; installed version 25.2.3.2 (Linux).
How this guide was checked
Selected example formulas were run in LibreOffice Calc 25.2.3.2 on Linux on October 12, 2026. The official Help may cover a newer version. Tests of selected formulas do not certify every locale, interactive array operation or file round trip. View the example-check record.