SUMSQ function

SUMSQ returns the sum of the squares of its arguments, useful for statistical analysis and variance calculations.

=SUMSQ(number1, ...)

Generate a SUMSQ formula

Describe what you need. The generator will reach for SUMSQ where SUMSQ is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How SUMSQ reads its arguments
number1requiredSUMSQ →
ArgumentRequiredDescription
number1RequiredRequired. The first number or range of numbers to square and sum. Can be a single cell, range, or literal value; SUMSQ ignores text and logical values in the range.
...RepeatingOptional. Additional numbers or ranges to include in the calculation; SUMSQ accepts up to 255 arguments total (number1 + repeating arguments).

Returns

Returns a number representing the sum of squared values from all provided arguments.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Measure overall budget variance magnitude

CategoryMonthBudgetedActualVariance
GroceriesJan500520-20
GroceriesFeb50048020
UtilitiesJan200210-10
UtilitiesFeb20019010
EntertainmentJan30028020
EntertainmentFeb300310-10
=SUMSQ(E2:E7)

Result: 1500

SUMSQ squares each variance (-20² + 20² + (-10)² + 10² + 20² + (-10)² = 400 + 400 + 100 + 100 + 400 + 100 = 1500). Squaring converts negatives to positives, giving a single measure of total deviation—how far actual spending strayed from budget across all categories.

2. Calculate penalty score for spending deviations

CategoryMonthBudgetedActualVariance
GroceriesJan500520-20
GroceriesFeb50048020
UtilitiesJan200210-10
UtilitiesFeb20019010
EntertainmentJan30028020
EntertainmentFeb300310-10
=SUMSQ(D2:D7-C2:C7)

Result: 1500

The formula calculates (Actual - Budgeted) differences for each row, then squares them: 20² + (-20)² + 10² + (-10)² + (-20)² + 10² = 1500. By squaring, larger deviations receive heavier weight—months where spending control was weakest are penalized more heavily than small oversights.

3. Sum squared actual spending for statistical analysis

CategoryMonthBudgetedActualVariance
GroceriesJan500520-20
GroceriesFeb50048020
UtilitiesJan200210-10
UtilitiesFeb20019010
EntertainmentJan30028020
EntertainmentFeb300310-10
=SUMSQ(D2:D7)

Result: 755500

SUMSQ squares each actual spending value: 520² + 480² + 210² + 190² + 280² + 310² = 755500. This sum of squares is a building block for statistical measures like variance and standard deviation, helping identify the overall magnitude of spending patterns.

Common errors

Which SUMSQ error are you seeing?
SUMSQ returned an error#VALUE!
Replace the problematic cell with a numeric value, remove it from the range, or wrap the formula in IFERROR to handle non-numeric values: =IFERROR(SUMSQ(E2:E7), 0).
#REF!
Update the formula to reference existing cells, or use Ctrl+Z to undo the deletion if the data is still needed elsewhere.
#NULL!
Use a colon (:) to define ranges: =SUMSQ(A1:A5). Use commas (,) only to separate distinct arguments: =SUMSQ(A1:A5, C1:C5).
ErrorWhy it happensHow to fix it
#VALUE!A cell in your range contains text (like 'Pending') or a formula error (like #DIV/0!) that cannot be converted to a number.Replace the problematic cell with a numeric value, remove it from the range, or wrap the formula in IFERROR to handle non-numeric values: =IFERROR(SUMSQ(E2:E7), 0).
#REF!The range reference points to deleted columns or rows (e.g., you deleted column D but the formula still references D2:D7), or the range address is invalid.Update the formula to reference existing cells, or use Ctrl+Z to undo the deletion if the data is still needed elsewhere.
#NULL!You used incorrect range syntax, such as separating ranges with a space (SUMSQ(A1 A5)) instead of a colon, or a comma outside the intended grouping.Use a colon (:) to define ranges: =SUMSQ(A1:A5). Use commas (,) only to separate distinct arguments: =SUMSQ(A1:A5, C1:C5).

Tips and when to use something else

  • SUMSQ squares each value before summing, making it ideal for calculating variance components and statistical measures where negative and positive deviations should both contribute equally to the result.
  • For weighted sums or flexible calculations without automatic squaring, use SUMPRODUCT instead, which multiplies across ranges and then sums—useful for cost-benefit analysis or weighted averages.
  • If you need the sum of absolute values without squaring, use =SUM(ABS(range)) or SUMPRODUCT(ABS(range)) instead of SUMSQ.
  • SUMSQ works seamlessly with array formulas in Google Sheets and modern Excel, allowing complex statistical and risk-scoring models without requiring Ctrl+Shift+Enter.

Related functions

SUMSUM adds numeric values in a range or multiple ranges, ignoring text and empty cells, and returns the total sum.SUMPRODUCTSUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.POWERRaises a number to a specified power, returning the exponential result needed for growth calculations, depreciation, and scaling models.ABSABS returns the absolute value of any number, removing the sign to show only magnitude, useful for comparing distances regardless of direction.AGGREGATEReturns the result of a function (like SUM, AVERAGE, COUNT) applied to a range, with built-in options to ignore error values, hidden rows, and nested SUBTOTALs.

Frequently asked questions

What's the difference between SUMSQ and SUMPRODUCT?
SUMSQ specifically squares each value before summing, while SUMPRODUCT multiplies corresponding elements across ranges before summing. Use SUMSQ for statistical calculations emphasizing magnitude; use SUMPRODUCT for flexible weighted calculations like cost-benefit analysis or weighted averages.
Does SUMSQ work with negative numbers?
Yes. Since SUMSQ squares each value (negative × negative = positive), all results contribute positively to the sum regardless of their original sign. This makes SUMSQ useful for measuring total deviation where direction doesn't matter.
When would I actually use SUMSQ in business or finance?
SUMSQ is used for variance analysis, risk scoring, penalty calculations, and any situation where you need to emphasize larger deviations. Examples include budget deviation penalties, spending volatility analysis, or statistical quality controls.
Does SUMSQ support arrays in Google Sheets and modern Excel?
Yes, SUMSQ works natively with arrays in Google Sheets and Excel 365. You can use it directly with array formulas without Ctrl+Shift+Enter, making complex statistical calculations simpler and more readable.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17