How to Calculate Sum of Squares in Excel (6 Quick Tricks)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel provides us with the option to calculate the Sum of Squares very easily. We can also compute the Sum of Squares using mathematical formulas in excel. In this article, we will see how we can find Sum of Squares quickly in simple methods in Excel.


1. Calculating Sum of Squares for Multiple Cells in Excel

Here, we will calculate the sum of squares for multiple cells using the SUMSQ Function. The SUMSQ Function returns the sum of squares of a series of values.

To make the explanation easier to understand we will use a dataset of some random record of numbers. The dataset has 3 columns; these are Record 1, Record 2 & Sum of Squares.

Sum of Squares for Multiple Cells in Excel

STEPS:

  • Select Cell D5 at first.
  • Now type the formula:
=SUMSQ(B5,C5)
 

Sum of Squares for Multiple Cells in Excel

  • Next, press Enter to see the result in Cell D5.

Sum of Squares for Multiple Cells in Excel

  • Now, use the Fill Handle tool to see the results in the next cells.

  • Finally, we can see results in all cells.

Read More: All the Easy Ways to Add up (Sum) a column in Excel


2. Using the Basic SUM Function

In this method, we will find the Sum of Squares by using the SUM Function. The SUM Function adds the numerical values in a range of cells.

We will use the same dataset used in Method 1.

STEPS:

  • Firstly, select Cell D5.
  • Now type the formula:
=SUM(B5^2,C5^2)
  Calculating the Sum of Squares Using Sum in Excel
  • Then press Enter to see the result.

Calculating the Sum of Squares Using Sum in Excel

  • Finally, use the Fill Handle at Cell D5 and drag it till Cell D12 to see all results.

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)


3. Finding the Sum of Squares with Cell Ranges in Excel

We can also compute the sum of squares in a range of cells in Excel. It is very helpful when you have a large dataset.

We will use a dataset of numbers in this method. The dataset has 2 columns; these are List 1 & List 2. We will see the sum of squares in the last row of the table.

Finding Sum of Squares with cell ranges in Excel

STEPS:

  • Select Cell B10 at first.
  • Next type the formula:
 =SUMSQ(B5:B9)

Finding Sum of Squares with cell ranges in Excel

It will calculate the sum of squares from Cell B5 to Cell B9.

  • Now press Enter and you can see the result.

  • Use the Fill Handle to see the results in the adjacent cell.

Read More: How to Add Numbers in Excel (2 Easy Ways)


Similar Readings


4. Calculating the Sum of Squares after Inserting Math Formula in Excel

Excel also gives us the freedom to calculate the sum of squares after performing any mathematical operation.

In this method, we will use a dataset of 3 columns; these are Data 1, Data 2 & Sum of Squares.

STEPS:

  • In the first place, select Cell D5.
  • Now put the formula :
=SUMSQ(B5/C5,B5*C5)

Calculating Sum of Squares after inserting Math Formulas in Excel

Note: It will first divide Cell B5 by Cell C5 and store the value in the first argument. Then it will multiply Cell B5 with Cell C5 and store the value in the second argument. The SUMSQ Function will find the square of the two arguments and add them.

  • Then press Enter to see the result.

Calculating Sum of Squares after inserting Math Formulas in Excel

  • Finally, use the Fill Handle to AutoFill the rest of the cells.

Calculating Sum of Squares after inserting Math Formulas in Excel


5. Using IF Function to Find the Sum of Squares in Excel

Sometimes we face certain criteria to calculate the sum of squares. In those cases, we can use the IF function inside the SUMSQ function to find the sum of squares.

Suppose, we will calculate the sum of squares if the value of the first argument of the SUMSQ function is greater than 10. We will use the previous dataset in this method.

STEPS:

  • Select Cell D5 at first.
  • Now type the formula:
=SUMSQ(IF(B5>10,B5,0),C5)

Use of IF Function to find Sum of Squares in Excel

Note: If the value of Cell B5 is greater than 10, it will take the input in the first argument. Otherwise, it will take 0 as input.

  • Next, press Enter to see the result.

Use of IF Function to find Sum of Squares in Excel

  • Finally, use the Fill Handle tool to AutoFill the formula for the rest of the cells.

Use of IF Function to find Sum of Squares in Excel

Read More: Excel Sum If a Cell Contains Criteria (5 Examples)


6. Computing Sum of Squares by Multiplication in Excel

We can also compute the sum of squares by multiplying the cell by itself in Excel. After multiplication, we just need to add the values by the SUM Function. It is another easy way to find the sum of squares which we will apply in the below article.

We will use the previous dataset here.

STEPS:

  • Firstly, select Cell D5.
  • Secondly, type the formula:
=SUM(B5*B5,C5*C5)
Computation of Sum of Squares by Multiplication in Excel
  • Thirdly, press Enter to see the result.

Computation of Sum of Squares by Multiplication in Excel

  • Lastly, use the Fill handle tool to AutoFill the remaining cells.


Download Practice Workbook


Conclusion

By using these methods, we can calculate the Sum of the Squares very quickly. We can also use these methods if we face certain conditions. A practice book is also added above for your use. Finally, if you have any queries or suggestions, please feel free to comment below.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo