How to use SUM and SUMSQ functions in Excel
Understanding how to use SUMIF and SUMIFS functions in Excel is easier than you think. However, to properly use each of these functions you first need to learn what are the parameters used for computation and the expected result.
After reading this article you will be able to use each of the functions with ease.
How to use SUM and SUMSQ functions
SUM and SUMSQ are some of the most basic Excel functions. They are easy to use and there are no specific parameters or conditions required.
The SUM function adds up all the numbers and returns the total amount, while the SUMSQ function takes the square value of each number and returns the total amount resulted from adding all the values. The syntax is the same for both functions.
Syntax:=SUM(number1 ,[number2], ...)=SUMSQ(number1, [number2], ...)
You can use numbers (like 5 or 11), cells (D1, F3, and so on), ranges, or even cell or range names. Please keep in mind that you are limited to a maximum of 255 numbers.
Now let’s dive into the actual formula and look at some examples of how to use those functions.
Examples using SUM and SUMSQ functions
Example 1
Let’s assume we need to calculate the salaries of all employees. We can easily achieve this by using the SUM function:=SUM(G2:G25)
To select the range you just need to select G2 and with your left mouse click pressed drag all the way down to G25. Then close the brackets (parenthesis) and hit Enter.
Example 2:
You can also name a range of cells and use that name in the SUM function. For this example, we have named G2:G25 as “Salaries” and used the formula =SUM(Salaries) to calculate the total annual salaries. The result is the same as in Example 1, the only difference is the method used for achieving this.
Example 3
Additionally, in our file, we also calculated the total annual salaries for employees working in IT. For this we used the following code:=(G3, G7, G15:G17, G21, G24)
In this case, instead of a single range we just picked the cells we were interested in and skipped the rest of the divisions. As you can see you can combine cells with ranges in the same SUM formula.
However, this method is prone to human errors since it requires you to manually select the salaries of employees working in IT. This means that you can easily select the wrong cell or miss one that should have been included. This is when more advanced and versatile functions like SUMIF or SUMPRODUCT come into play.
Example 4
Another quick way to sum all the cells from a table is to use the Autosum option from the Excel menu. For this, all you need to do is select the cell underneath the last value on the column and press Autosum. You can also make a selection of the cells you want to add up and press the button. The Autosum option is available in the Home tab of the Ribbon, in the Editing section.
Keep in mind that this only works with continuous ranges. This means that if you have an empty cell on your column Excel will only take into account the last range starting from the empty cell and ending at the bottom of the column. If you want to add up all the values from a column you cannot use the Autosum option since it will not return the correct value.
Example 5
Using SUMSQ we can easily calculate the square values from cells or range and sum all the values. In this example we have taken cells L9, L10, L11, and L12 and used the formula =SUMSQ(L9:L12). This can be translated into 2^2 + 3^2 + 4^2 + 5^2 = 4 + 9 + 16 + 25 = 54. Just like the SUM function you can use values, cells, arrays, or a mix of those. For example, you can type =SUMSQ(2, 3, A8, C11:C20) and the function will still work correctly.
What to do next?
I hope that after reading this article you can better understand how those functions work. In case you need more details or have questions on how to use them in your projects, please leave a comment and I will get back to you as soon as possible.
Alternatively, I recommend that you also read the following articles using SUMIF and SUMIFS:
It is useful. Thanks!
I am glad it was helpful.