SUMX2MY2 Function (LibreOffice Calc)

=SUMX2MY2(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:=SUMX2MY2 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]

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.

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.

=SUMX2MY2(A2:A6;B2:B6)

Observed −24 in Calc 25.2.3.2. The five row contributions are 9−4 = 5, 25−25 = 0, 49−36 = 13, 81−100 = −19, and 121−144 = −23. Their sum is 5 + 0 + 13 − 19 − 23 = −24. Equivalently, 285 − 309 = −24. This is an independent arithmetic calculation that was then confirmed by native execution. [NATIVE]

The order matters. If you reverse the arguments, =SUMX2MY2(B2:B6;A2:A6) returns 24, not −24. Both formulas were natively asserted.

Audit the formula using ordinary square sums. These two formulas returned the same result in the native test workbook:

=SUMX2MY2(A2:A6;B2:B6)
=SUMSQ(A2:A6)-SUMSQ(B2:B6)

Both produce −24 for purely numeric, aligned arrays. The algebraic identity is Σ(xᵢ² − yᵢ²) = Σxᵢ² − Σyᵢ²; the equivalence assumes the same contributing numeric observations. [NATIVE]

Compare negative and zero values. Enter X in A10:A14 as −3, −2, 0, 2, 3; enter Y in B10:B14 as −2, −2, 0, 2, 2. Then:

=SUMX2MY2(A10:A14;B10:B14)

Observed 10: the X squared total is 26, the Y squared total is 16. A zero is an ordinary contributing value. Signs disappear within each square, but the difference of totals retains its sign. [NATIVE]

Identical numeric arrays: =SUMX2MY2(A2:A6;A2:A6) returned 0; =SUMX2MY2(5;3) returned 16, but this single-scalar input is an observed Calc behavior rather than a separate documented alias. [NATIVE]

Don’t confuse with squared differences. The closely named SUMXMY2 returns 4 on the same practice data, because it squares each difference first. That is the right function for a sum-of-squared-errors calculation; SUMX2MY2 computes something mathematically different.

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.

Important text-data difference: With two numeric columns interrupted by text entries, the three related functions did not respond identically in the native observations. SUMX2MY2(D2:D6;E2:E6) yielded −9 with text-interrupted rows, while the analogous SUMXMY2 yielded #VALUE!. 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 Calc 25.2.3.2 natively executed the examples above. LibreOffice 26.8 Help describes the same SUMX2MY2 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.

• 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.

• [LO] LibreOffice 26.8 Help — Array Functions, section “SUMX2MY2”; 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 — SUMX2MY2 function; checked 2026-10-11. https://support.microsoft.com/en-us/excel/functions/sumx2my2-function — comparable Excel definition and documented Excel error behavior; not natively tested here.
• [GS] Google Sheets Help — SUMX2MY2; checked 2026-10-11. https://support.google.com/docs/answer/3094257?hl=en — comparable two-array definition; not natively tested here.
• [NATIVE] native-results.json + run_native.py + SUMX_native_tests.ods in 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).

Compare the paired-array operations: SUMX2PY2 · SUMXMY2.

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.

Official SUMX2MY2 reference · Editorial standards