Syntax
SUMXMY2(array_x, array_y)
| Parameter | Description |
|---|---|
| array_x | Parameter of the SUMXMY2 function. |
| array_y | Parameter of the SUMXMY2 function. |
Examples
Sum of squared errors
Formula
=SUMXMY2(A2:A6, B2:B6)
If A2:A6 has actual values {10,20,30,40,50} and B2:B6 has predicted values {12,18,33,39,48}, returns 4+4+9+1+4 = 22. This is the SSE for a regression model.
Euclidean distance squared
Formula
=SUMXMY2({3,7}, {0,3})
Returns (3-0)²+(7-3)² = 9+16 = 25. Take SQRT to get the actual Euclidean distance of 5.
Comparing two datasets
Formula
=SUMXMY2(C2:C20, D2:D20)
Measures how different two columns of measurements are. Lower values mean the datasets are more similar.
Common Errors
#N/A
The two arrays must be the same size. Unequal-length arrays return #N/A.
#VALUE!
Non-numeric values in either array cause this error. Ensure all cells contain numbers.
Tips
Calculate RMSE easily
RMSE = SQRT(SUMXMY2(actuals, predictions) / COUNT(actuals)). This is one of the most common model evaluation metrics.
Always non-negative
Because differences are squared, SUMXMY2 always returns 0 or a positive number. A result of 0 means the arrays are identical.
Prefer over manual formulas
Instead of creating a helper column with =(A2-B2)^2 and summing it, use SUMXMY2 directly — it's cleaner and faster on large datasets.
Try SUMXMY2 in Viztab
Import your data and use SUMXMY2 with 370+ other formulas. No signup required.
Open Viztab