SUMXMY2 Function (LibreOffice Calc)
Learn the SUMXMY2 function in LibreOffice Calc: Returns the sum of squares of differences 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: SUMXMY2 adds the squared differences between corresponding X and Y values: Σ(X−Y)². For the example sheet, =SUMXMY2(A2:A6;B2:B6) returns 4 because the five differences are 1, 0, 1, −1, −1, whose squares sum to 1+0+1+1+1. [LO] [NATIVE]
This is a practical building block for sum of squared errors (SSE) in forecasts, calibration, fitted models and quality control. It is not yet a mean squared error or root mean squared error; those require a division or square-root step. Squaring means that an error of 2 contributes 4, whereas an error of 1 contributes 1.
Exact Calc syntax, required arguments and locale
=SUMXMY2(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:=SUMXMY2 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: sum of squared forecast errors
=SUMXMY2(A2:A6;B2:B6)
Observed 4 in native Calc 25.2.3.2. The per-row residuals (A−B) are 1, 0, 1, −1, −1, and the squared residuals are 1, 0, 1, 1, 1. Each input pair contributes a nonnegative amount, regardless of whether the prediction overshoots or undershoots. The result was independently calculated and natively verified. [NATIVE]
Advanced examples: MSE, RMSE, vector distance and checks
Mean squared error (MSE). If A2:A6 and B2:B6 contain exactly five complete numeric pairs, divide SSE by five:
=SUMXMY2(A2:A6;B2:B6)/COUNT(A2:A6)
Observed 0.8 in the native workbook. COUNT(A2:A6) was 5 for these numeric rows; this denominator would be incorrect if the pair count were different, such as with missing or text observations in either input column. Clean and count valid pairs first. [NATIVE]
Euclidean distance. For those vectors, =SQRT(SUMXMY2(A2:A6;B2:B6)) returns 2, because √4=2 (native-tested). For full RMSE, the mathematically appropriate formula is =SQRT(SUMXMY2(A2:A6;B2:B6)/COUNT(A2:A6)), approximately 0.894427 for these five complete pairs; this full composite was independently calculated but not separately executed as an assertion in the recorded checks. [NATIVE]
Equivalent direct product. For the same numeric, equal-sized ranges, =SUMPRODUCT((A2:A6-B2:B6)*(A2:A6-B2:B6)) returned 4 in native Calc and matches the SSE result. The multiplication must be applied to corresponding elements before addition. [NATIVE]
Signed and swapped inputs. With X=−3, −2, 0, 2, 3 and Y=−2, −2, 0, 2, 2, =SUMXMY2(A10:A14;B10:B14) returned 2. Swapping A2:A6 and B2:B6 preserves the result 4 because (x−y)²=(y−x)². Equal inputs yield 0. =SUMXMY2(5;3) returned 4 in the native test. [NATIVE]
Why the near-identical names are different: SUMX2MY2 computes −24 and SUMX2PY2 computes 594 on the same five-row sheet. Their exponents apply to the individual values before addition or subtraction. Here SUMXMY2 squares the difference first and returns 4, a quantity suitable for SSE.
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 SUMXMY2. |
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. SUMXMY2(D2:D6;E2:E6) returned #VALUE! / code 519 when paired inputs contained text; this differs from the numeric outputs observed for SUMX2MY2 and SUMX2PY2. 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 SUMXMY2 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 “SUMXMY2”; 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 — SUMXMY2 function; checked 2026-10-11. https://support.microsoft.com/en-us/excel/functions/sumxmy2-function — comparable Excel definition and documented Excel error behavior; not natively tested here.
- [GS] Google Sheets Help — SUMXMY2; checked 2026-10-11. https://support.google.com/docs/answer/3094298?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.