How to Use SUMSQ Function in Excel

This guide will explain how to use the SUMSQ function in Excel.

We can use the SUMSQ function to return the sum of the squares of a range of numbers.

The rules for using the SUMSQ function in Excel are as follows:

• The function requires one or more numerical arguments. The user may supply a constant or a cell reference as input.
• The function then outputs a numerical value that is equal to the sum of the squares of each numerical value specified by the user.

Let’s look into a possible scenario where you may need to use the SUMSQ function.

Suppose you have a dataset of 50 random participants. You need to determine the dispersion of several variables among the given sample population.

For example, you may want to determine the variance of height among the sample. We can use the sum of squares metric to determine the spread of data around the sample mean.

If we already have the distance between each data point and the mean, we can find the total of each distance squared.

We can find the sum of the squares of a given range of values through Excel’s built-in SUMSQ function. 

This is just one possible use case for the SUMSQ function. For example, we can use the SUMSQ function to find the sum of the first N natural numbers squared.

Instead of having to type 1^2 + 2^2 … N^2, you can create a range of values from 1 to N and use that range as input for SUMSQ.

Now that we know when to use the SUMSQ function, let’s learn how to use it and see how it works on an actual sample spreadsheet.

The Anatomy of the SUMSQ Function

The syntax of the SUMSQ function is as follows:

=SUMSQ(number1, [number2], ...)

Let’s look at each term to understand what they mean.

• = the equal sign is how we start any function in Excel.
• SUMSQ() is our SUMSQ function. This function calculates the sum of the squares of each argument.
• number1 and [number2] refer to the first and second numerical arguments SUMSQ function can accept. The SUMSQ function may also have more than two arguments.
• Instead of separating each argument with a comma, you may also provide a cell range.
• Empty cells, Boolean values, text, and error values are ignored by the SUMSQ function.

A Real Example of Using SUMSQ Function in Excel

This section will go through a basic example of the SUMSQ function in use.

First, let’s take a look at our sample dataset. We have a table of 10 values that we’ll use to find the sum of squares.

We’ll use the SUMSQ function to determine the sum of squares of the cell range A2:A11. In this example, our range outputs a result of 1330.

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

Use our sample spreadsheet to see how the SUMSQ function’s output will change given different values in your dataset.

If you’re ready to try out the SUMSQ function in Excel, head over to the next section to read our step-by-step breakdown on how to do it!

How to Use SUMSQ in Excel

This section will guide you through each step to start using the SUMSQ function. You’ll learn how to use this statistical function to compute the variance of a sample population.

Follow these steps to start using the SUMSQ function:

1. First, select the cell that will compute the sum of squared deviations. In this example, we will use cell E2.
   
2. Type ‘=SUMSQ(‘ into the formula bar to start the SUMSQ function.
   
3. Add the cell range you want to measure as the argument of the SUMSQ function.
   
4. Hit the Enter key to evaluate the SUMSQ function.
   
5. We can confirm that the SUMSQ function returns the correct result by performing the operation ourselves. Create a second column that returns the square of each value. Find the sum of the entire second column using the SUM function.
   
   

This step-by-step guide should provide you with all the information you need to begin using the SUMSQ function.

You should now have a better understanding of how the SUMSQ function works. We can use this function to find the sum of the squares of a set of values without having to compute each of these squares on the spreadsheet.

The SUMSQ function is just one example of the many Excel functions you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe