SUMXMY2 Function

Returns the sum of squared differences between two arrays (Σ (x − y)²).

Category

Math & Trig

Available in

All versions, Microsoft 365

Returns

A number

Want to practice SUMXMY2 in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

SUMXMY2 subtracts each pair, squares the difference and adds them up.

Compare forecast with actual and you have the sum of squared errors — the heart of many accuracy measures.

Syntax

=SUMXMY2(array_x, array_y) Explain

Arguments

ArgumentRequired?What it does
array_xYesThe first range.
array_yYesThe second range, same size.

Examples

Example 1 — Forecast accuracy

Squared differences between forecast and actual sales.

C2fx=SUMXMY2(A2:A4, B2:B4)
ABC
1ForecastActualSquared error
26006201700
3500480
4400430
=SUMXMY2(A2:A4, B2:B4) Explain

20² + 20² + 30² = 400 + 400 + 900 = 1,700.

Common mistakes

  • Different sizes. Return #N/A.

Notes

  • Related functions: SUMX2MY2, SUMX2PY2, SUMSQ.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.