Syntax
SUMX2MY2(array_x, array_y)
| Parameter | Description |
|---|---|
| array_x | Parameter of the SUMX2MY2 function. |
| array_y | Parameter of the SUMX2MY2 function. |
Examples
Paired measurement comparison
Formula
=SUMX2MY2(A2:A4, B2:B4)
If A2:A4 is {3,5,7} and B2:B4 is {2,4,6}, returns (9-4)+(25-16)+(49-36) = 5+9+13 = 27. Shows the total squared dominance of the first set.
Before vs after analysis
Formula
=SUMX2MY2(C2:C6, D2:D6)
Compares squared before-values minus squared after-values. A positive result means the 'before' measurements were larger in magnitude overall.
Two-column data
Formula
=SUMX2MY2({10,20,30}, {5,15,25})
Returns (100-25)+(400-225)+(900-625) = 75+175+275 = 525. Inline arrays work too.
Common Errors
#N/A
The two arrays must have the same number of elements. If A2:A4 has 3 values but B2:B5 has 4, you get #N/A.
#VALUE!
Non-numeric entries in either array cause this error. Text or blank cells in the ranges will fail.
Tips
Relationship to difference of squares
Remember that x²−y² = (x+y)(x−y). This formula effectively sums those products across paired data.
Arrays must match
Both arrays must be the same length. If your data has gaps, clean it first or use helper columns to align the pairs.
Combine with SUMX2PY2
Use SUMX2MY2 alongside SUMX2PY2 to get both the sum of differences and sum of totals of squares for a complete paired analysis.
Try SUMX2MY2 in Viztab
Import your data and use SUMX2MY2 with 370+ other formulas. No signup required.
Open Viztab