SUMXMY2 Function in Excel: Sum of Squared Differences

SUMXMY2 Function in Excel: Sum of Squared Differences 1
⏱ 1 min readUpdated 5 October 2026

Maths & trigLevel: ExpertAvailable in: Excel 2007+

In this article
  1. Syntax
  2. Examples
  3. Example 1
  4. SUMXMY2 in practice
  5. Where you will use it
  6. Worked example
  7. Mistakes people make
  8. Related functions

SUMXMY2 returns Σ(x – y)²: the squared error between forecasts and actuals.

Advertisement

Syntax

=SUMXMY2(array_x, array_y)
Argument What it means
array_x, array_y Same-size ranges

Examples

Example 1

=SUMXMY2(CHOOSE(A2,{100,120,130},{1,2,3},{5,5}),CHOOSE(A2,{98,125,128},{1,1,1},{3,4}))

Result: 33. See the worked example table below for more cases.

SUMXMY2 in practice

Where you will use it

  • Forecast error (sum of squared errors)
  • Distance between two vectors
  • Model fitting

Worked example

CaseFormulaResult
1=SUMXMY2(CHOOSE(A2,{100,120,130},{1,2,3},{5,5}),CHOOSE(A2,{98,125,128},{1,1,1},{3,4}))33
2=SUMXMY2(CHOOSE(A3,{100,120,130},{1,2,3},{5,5}),CHOOSE(A3,{98,125,128},{1,1,1},{3,4}))5
3=SUMXMY2(CHOOSE(A4,{100,120,130},{1,2,3},{5,5}),CHOOSE(A4,{98,125,128},{1,1,1},{3,4}))5

Results calculated in Excel.

Mistakes people make

MistakeWhat to do instead
Different sizes#N/A
Want RMSESQRT(SUMXMY2(a,f)/COUNT(a))

SUMSQ · STEYX

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Keep reading

Excel Functions A–ZAVERAGEIF Function in Excel: Average With One ConditionExcel Functions A–ZF.DIST Function in Excel: F Distribution (Left Tail)Excel Functions A–ZDETECTLANGUAGE Function in Excel: Which Language Is This?
Advertisement
✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong