SUMSQ Function in Excel

The SUMSQ function in Excel squares each number and returns the sum of those squares.

It saves a separate squaring step when working with distances or statistical calculations. Squaring makes negative and positive inputs contribute positive amounts to the total.

SUMSQ squares the original values; it does not first subtract their average.

In this article, I’ll show you how to calculate squared errors, sum squares for matching rows, and find straight-line distance.

SUMSQ Function Syntax in Excel

The SUMSQ function accepts numbers, cell references, ranges, or arrays.

=SUMSQ(number1, [number2], ...)
  • number1 is required. It is the first number, range, or array whose values you want to square and add.
  • number2 is optional. You can supply additional numbers, ranges, or arrays, up to 255 arguments in total.

SUMSQ squares each numeric value and returns the sum of those squares.

When to Use SUMSQ Function

  • Add the squares of values in one or more ranges.
  • Measure total squared differences between two sets of numbers.
  • Calculate a squared total for rows that meet a condition.
  • Combine squared components before finding a distance.

Example 1: Sum Squares in One Range

Let’s begin with a basic range formula.

Below is the dataset with returned parcel counts for five weekdays.

Dataset for SUMSQ example 1

I want to square each daily count and add the five squared values.

Here is the formula:

=SUMSQ(B2:B6)
=SUMSQ(B2:B6) in E2

Excel calculates 4² + 6² + 5² + 3² + 7². Those squared values add up to 135.

You only need the original values in the worksheet. SUMSQ performs the square-and-add calculation inside the formula.

Pro Tip: Negative numbers still contribute positive squares. For example, both 4 and -4 add 16 to the result.

Example 2: Use SUMSQ With Multiple Ranges

SUMSQ can combine separate ranges in one calculation.

Below is the dataset with stock adjustments for two warehouse zones across four weeks.

Dataset for SUMSQ example 2

I want one sum of squares for all adjustments in Zone A and Zone B.

Here is the formula:

=SUMSQ(B2:B5,D2:D5)
=SUMSQ(B2:B5,D2:D5) in G2

The first range contributes 33 because 2² + 3² + 4² + 2² equals 33.

The second range contributes 46. SUMSQ combines both sets and returns 79.

You can supply individual cells, constants, arrays, and ranges in the same formula when needed.

Example 3: Calculate Sum of Squared Errors

Here’s a useful way to measure the combined size of several differences.

Below is the dataset with scheduled and actual minutes for six service calls.

Dataset for SUMSQ example 3

I want to calculate the sum of the squared timing errors across all six calls.

Here is the formula:

=SUMSQ(C2:C7-B2:B7)
=SUMSQ(C2:C7-B2:B7) in F2

Excel subtracts scheduled minutes from actual minutes for each row. The differences are -1, 2, 1, -1, 3, and -2.

SUMSQ squares those six differences and adds them. The result is 20.

That is the total squared error. To calculate mean squared error, divide it by the number of observations.

Squaring prevents positive and negative errors from canceling each other out.

Pro Tip: Use a helper column when you also need to inspect each error. The array formula is better when you only need the combined squared total.

Example 4: Sum Squares for Matching Rows

You can place FILTER inside SUMSQ to calculate only the rows you choose.

Below is the dataset with rework hours for North and South teams, plus a cell that selects the team to analyze.

Dataset for SUMSQ example 4

I want the sum of squares for the North team’s rework hours.

Here is the formula:

=SUMSQ(FILTER(B2:B9,A2:A9=D2))
=SUMSQ(FILTER(B2:B9,A2:A9=D2)) in E2

FILTER returns the North values 2, 3, 1, and 5. SUMSQ then calculates 2² + 3² + 1² + 5² and returns 39.

Although FILTER can return several values, SUMSQ reduces them to one result.

FILTER is available in Excel 2021, Excel 2024, and Microsoft 365. Older versions need a helper range or another criteria-based approach.

Example 5: Calculate Straight-Line Distance

SUMSQ also fits neatly inside the distance formula for a right triangle.

Below is the dataset with the horizontal and vertical distances for a cable run.

Dataset for SUMSQ example 5

I want to calculate the straight-line distance from the two perpendicular measurements.

Here is the formula:

=SQRT(SUMSQ(B2,C2))
=SQRT(SUMSQ(B2,C2)) in F2

SUMSQ squares 9 and 12, then adds them to get 225. SQRT returns the positive square root of 225, which is 15 meters.

You could also use =SQRT(B2^2+C2^2). SUMSQ keeps the square-and-add portion compact when a formula has several components.

Tips & Common Mistakes

  • SUMSQ returns one value. It does not spill a list of squared results.
  • Negative values become positive after they are squared, so they increase the result.
  • SUMSQ squares raw values. DEVSQ first measures each value’s distance from the mean, then squares and adds those deviations.
  • When an argument is a range or array, SUMSQ ignores empty cells, logical values, text, and error values in that reference.
  • Text representations of numbers and logical values typed directly as arguments are counted. The same items in a referenced range are ignored.
  • A formula such as =SUMSQ(C2:C7-B2:B7) may need Ctrl+Shift+Enter in Excel 2019 and earlier.

I covered how SUMSQ handles ranges, squared errors, filtered values, and distance calculations.

I hope you found this article helpful.

List of All Excel Functions

Other Excel articles you may also like: