How to Calculate Sum of Squares in Excel

puneet-gogia-excel-champs

- Written by Puneet

Let’s say you’re a project manager tracking each team member’s hours on different tasks during a week.

To assess the variability in work hours and better manage workloads, you decide to calculate the sum of squares of deviations from the average hours worked.

In Excel, there is a specific function that you can use to calculate the sum of squares of numbers in a single cell.

This function is called SUMQ. And refer to a range of cells or can enter values directly into the function to get the result.

Formula to Get the Sum of Squares

This function calculates the sum of squares of the values in cells A1:A10. It directly squares each value and then sums them up.

sum-of-squares

Below are the steps to write this:

  1. Type (=) in a cell to enter the function.
  2. Enter the (SUMQ) and then enter the starting parentheses.
  3. Refer to the range where you have the numbers you want to get the sum value of squares.
  4. Close the function and hit enter to get the result.

When you hit enter, it gives you a final value, the sum of the square of 25 and 25, as we have two numbers in the range A1:B1.

sumsq-to-sum-squares

To verify, we can do a manual check. The square of 25 is 625, and as we have 25 in both cells, it should be 625 + 625 = 1250.

And this is what we have in the result. You can also refer to a range with multiple numbers without worrying about the count of cells.

manual-check-of-square

Note: If you are using negative numbers, you will still get the result as a positive number.

Use a Simple Maths Formula

Instead of using the function, you can use a simple formula using the caret operator (^).

math-formula-to-sum-squares
=SUM(A1^2,B1^2)

Here we have used the SUM function, and with each number, we have used the caret operator (^) to get the square of that number first. Or you can also write a formula like the following:

=A1^2+B1^2

These methods have limited use. So, if you have a long list of numbers, using the first method is better.

SUM of Square in Excel using Conditions

To calculate the sum of squares using a condition, you can use the SUMIFS function in combination with array formulas.

Here, I’ll show you how to calculate the sum of data squares based on a specific condition.

Let’s say you have sales data in column B and corresponding regions in column A. You want to calculate the sum of squares of sales, but only for the “North” region.

=SUM((B1:B10 * (A1:A10="North"))^2)

In older versions of Excel, you can enter it by using CTRL+SHIFT+ENTER after typing the formula.

You can enter it normally in newer versions (Excel 365 and Excel 2019), and Excel will handle it as a dynamic array if needed.

Last Updated: May 13, 2024