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
| Argument | Required? | What it does |
|---|---|---|
| array_x | Yes | The first range. |
| array_y | Yes | The second range, same size. |
Examples
Example 1 — Forecast accuracy
Squared differences between forecast and actual sales.
C2fx=SUMXMY2(A2:A4, B2:B4)
| A | B | C | |
|---|---|---|---|
| 1 | Forecast | Actual | Squared error |
| 2 | 600 | 620 | 1700 |
| 3 | 500 | 480 | |
| 4 | 400 | 430 |
=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.

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.