SUMXMY2 Function in Apple Numbers: Sum of Squared Differences

=SUMXMY2(set-1-values, set-2-values)

Both arguments appear as required in the documented Apple syntax; there is no documented default and no documented optional argument in this entry. They are collections: a contiguous numeric range such as A1:A6 is the clearest example. The expressions set-1-values and set-2-values are argument labels, not literal text to type into Numbers [A1].

What the function computes

For aligned values in A1:A6 and B1:B6, the mathematical operation is

SUMXMY2(A1:A6, B1:B6) = Σ(Aᵢ − Bᵢ)²

For the first pair, 2 − 7 = −5, and its squared contribution is 25. The subtraction occurs inside the square, making the result nonnegative for real-number data. Apple’s documentation provides the function’s name, both typed inputs, and a worked example [A1]; the algebraic expansion is independently checked [C1].

This guide uses Apple’s English Formulas and Functions Help 15.4 and its numerical example, checked 2026-10-12. The help edition is a documentation version, not proof that the function first shipped in Numbers 15.4 or is available in every older Mac, iPad, iPhone or iCloud release. Apple publishes the function in its Numeric category [A1, A5]. The calculations below were independently evaluated in a separate arithmetic model; there was no native Apple Numbers formula execution and no screenshot or measured application output. For a particular device, verify the function appears in that installed app’s Function Browser before relying on it for production work [A1, A5].

Use a normal Numbers table (not an Excel-style sheet-only cell grid). If the formula and data are in one table, A1:A6 and B1:B6 refer to its first two columns. For references in another table, Apple’s documented form is Table 2::A1:A6; if a table name or header wording makes a reference ambiguous, use the reference picker and qualify the table or sheet [A3]. The examples use English function names and comma argument separators. In regions where the decimal separator is a comma, Apple requires semicolons between function arguments: e.g. =SUMXMY2(A1:A6;B1:B6) [A2]. Function names are not case-sensitive [A2].

Why corresponding rows matter

These calculations pair the first numeric observation in the first collection with the first in the second collection, the second with the second, and so forth [A1]. Arrange observations in equal-length, corresponding rows before using the formula. Never sort only one input column, or the result will describe different pairs. The official function page names both arguments as collections containing number values, and it does not document a numeric default or an optional third argument [A1]. It does not, by itself, establish a specific Numbers error code or coercion rule for unequal-size arrays, strings, dates, Boolean cells, or missing observations. Don’t substitute Excel’s separate validation rules as evidence for Numbers [A1, X1].

Create a single Numbers table with no header rows for this demonstration (alternatively, use six ordinary data rows beginning at row 1). Enter the following numbers exactly in A1:B6:

1. Select a blank cell outside A1:B6 (for example D1).
2. Enter =SUMXMY2(A1:A6,B1:B6). The ordinary English-locale formula uses a comma between collection references [A1, A2].
3. The expected result from Apple’s own example is 196 [A1]. The pairwise calculations above independently reproduce this total [C1].
4. As a cross-check, inspect the per-row calculations. Do not enter a result formula inside A1:B6, because doing so would overwrite an input or create a circular dependency.

This fixture has 241 as the sum of A’s squares, 399 as the sum of B’s squares, and 222 as the A×B sum of products. The aligned pairs were independently calculated using integer arithmetic, not by running the Numbers formula [C1]. An equivalent independent mathematical check is SUMX2PY2(A1:A6,B1:B6) - 2*SUMPRODUCT(A1:A6,B1:B6); for this fixture it yields 196 [C1, A4].

Each test uses a separate table with exactly the rows shown, no header row, and one formula in a blank cell outside A:B. The right-hand output is an expected mathematical result, not an observed Apple Numbers output [C2].

For the second test, type the minus sign as a normal keyboard hyphen when entering negative numbers in Numbers. It is a calculated arithmetic test only; Apple’s single-paragraph function entry does not separately give all negative-number and zero-value corner cases [A1, C2].

Put measured and predicted values in two numeric columns of one Numbers table:

=SUMXMY2(A1:A3,B1:B3) has an expected output of 27 [C2]. This is the sum of squared errors (SSE). Unlike SUMX2MY2, swapping actual and predicted (=SUMXMY2(B1:B3,A1:A3)) does not change the result: squaring removes the sign [C2]. The value becomes larger when individual errors grow. It is not a signed bias and not an average by itself.

To obtain the mean squared error for these three known, valid numeric pairs, a separate formula is =SUMXMY2(A1:A3,B1:B3)/3 (expected 9), and the root-mean-squared error is =SQRT(SUMXMY2(A1:A3,B1:B3)/3) (expected 3). These denominator examples assume exactly three paired numbers. If data are missing or variable-length, verify the count of valid pairs first—never divide by all table rows without checking alignment [C2].

Independent identity and relation to squared magnitudes

For the same numeric pairs, Σ(Aᵢ−Bᵢ)² = Σ(Aᵢ²+Bᵢ²) − 2Σ(Aᵢ Bᵢ) [C3]. In Numbers function notation, that translates to SUMXMY2(A,B) = SUMX2PY2(A,B) − 2×SUMPRODUCT(A,B) under identical numeric pairing. The six-row example has SUMX2PY2=640 and SUMPRODUCT=222, so 640−2×222=196 [C1, C3]. This can help independently audit a model, but computing two additional functions is not necessary for ordinary use.

Result differs from the table above. Confirm that the six exact input pairs are present, each column contains real numeric values (not numbers imported as text), and the formula is placed outside the input range. With a header row, the data begin at row 2, so adjust both range ends together (e.g. A2:A7 with B2:B7) [A1, A3].

Unexpected value after sorting. The datasets represent matched observations. Sorting one column but not the other changes the pairings. Sort the entire table rows together. This is a data-design warning inferred from the matched-pairs definition, not an Apple-reported error message [A1, C3].

Input ranges have unequal lengths. Align the collections before evaluating; don’t pad one series with invented zeros or silently drop unmatched records. Microsoft’s Excel reference documents a specific unequal-length error, but the current short Apple help article does not specify the corresponding Numbers error token. We therefore do not claim a particular Numbers error code [A1, X1].

Blank, text, Boolean, date, and duration cells. Apple documents number values in both collections, not a complete coercion/ignore matrix. Clean and validate such inputs, and run controlled Numbers-native tests on the target release before relying on special handling. In particular, don’t transfer Excel’s blank or Boolean handling rules into a Numbers guide as though Apple had stated them [A1, X1].

Wrong calculation despite valid numeric data. If you expected the signed difference in squared magnitudes, you wanted SUMX2MY2, not SUMXMY2. If your result is unexpectedly high, inspect individual pairwise differences and check for mismatched observation orders or units. Check whether your application should measure change in total squared magnitudes, combined magnitude, or paired prediction error—these are three different questions [A1, A4].

Very large or very small numbers. Squaring scales values rapidly. Check that input magnitudes and units are appropriate; precision/range limits and underflow/overflow behavior are not quantified by this Apple help page. Verify against high-precision independent arithmetic when accuracy matters. This is a mathematical caution, not a claim that Numbers returns a specific overflow error [C3].

Excel supports SUMXMY2(array_x,array_y) and identifies the same sum-of-squared-differences operation [X1]. Excel’s particular treatment of blanks, logical cells and mismatched collection lengths cannot be assumed to describe Apple Numbers, whose short official page does not enumerate those cases [A1, X1].

Excel’s English names and operand order match this mathematical operation for numeric aligned ranges. Do not infer that Google Sheets, LibreOffice, WPS, Gnumeric, or other apps implement identical coercion, array behavior, localization, and version support without separately checking their vendor documentation. This guide’s example outputs are platform-independent mathematical predictions, not a cross-application execution report [A1, X1].

For decimal-comma locales in Numbers, the example becomes =SUMXMY2(A1:A6;B1:B6) [A2]. In localized Excel, translated function names and punctuation may differ; Microsoft’s English help page is not evidence of a locale-independent name [X1].

• Apple: SUMSQ squares numeric values in one or more collections; it supports the independent identities above [A4].
• Apple: SUMPRODUCT computes aligned products and provides an independent check on paired squared differences [A4].
• Apple: SUMX2MY2 subtracts the squares first and then sums. [A4]
• Apple: SUMX2PY2 adds corresponding squares. [A4]
• Apple: SUMXMY2 squares corresponding differences. [A4]

[A1] Apple, SUMXMY2, English Formulas and Functions Help, version 15.4; accessed 2026-10-12. Supports function name, required argument order/types, purpose, and six-row example/expected result; does not establish native test outcomes, exact version introduced, or undocumented corner-case behavior.

[A2] Apple, Functions overview, accessed 2026-10-12. Supports required/optional argument notation, comma-versus-semicolon locale rule, and case-insensitive function names.

[A3] Apple, Refer to cells in formulas, accessed 2026-10-12. Supports Numbers table range notation, cross-table :: references, and header-related reference behavior.

[A4] Apple, SUMSQ, SUMPRODUCT, and other members of the SUMX family, accessed 2026-10-12. Supports existence and function names. The algebraic identities and calculated example totals are independently proven in [C1–C3], not asserted as verbatim Apple descriptions.

[A5] Apple, List of functions by category, checked 2026-10-12. Supports inclusion in the current official Numeric functions list. Does not prove minimum Numbers release availability.

[X1] Microsoft, SUMXMY2 for Excel, accessed 2026-10-12. Used only to check the English Excel counterpart; any Excel-specific error/coercion rules are explicitly not attributed to Numbers.

[C1] Independent integer calculation, 2026-10-12: test fixture A=(2,9,3,11,1,5), B=(7,5,6,8,12,9); sum of A²=241; sum of B²=399; sum of products A·B=222; SUMXMY2 expected 196. Cross-checked with Apple’s published result [A1]. Method: Python integer arithmetic, not Apple Numbers execution.

[C2] Independent integer calculation, 2026-10-12: A=(1,2,3), B=(4,5,6) yields 27; A=(1,0,−2), B=(0,−2,3) yields 30. Method: Python integer arithmetic, not Apple Numbers execution.

[C3] Elementary algebra and matched-observation reasoning. Its conclusions can be verified symbolically; no platform test or benchmark is claimed.

Unequal lengths, numeric-looking text, blanks, Boolean inputs and header behavior were not tested in an installed Numbers release. Verify relevant edge cases on your target device and locale before depending on them.