As the name suggests, SUM function in Excel is used to sum-up all the numbers supplied in the arguments or in the range of cells. It is one of the most common used function in Excel.
The function is so easy that even a small kid or a beginner can use this function while getting started with Excel without any trouble. It can be used when need to add all the numbers provided in the range.
Syntax & Arguments
=SUM(number1, [number2], [number3],…)
number1 – The first numeric value that is required can either be range of cells, cell references to the location in the worksheet, or can directly supplied in the argument.
[number2], [number3], … [number255] – (Optional) The additional numeric values to be summed up and maximum 255 arguments can be supplied to the function. The values can be supplied directly to the argument, cell reference, or can be range of cells.
- Function returns an error value if any argument consists any type of error value.
- Function ignores empty cells, text entries, and logical value when supplied as an array or cell references in the argument
In the below example, we have summed up the values directly providing the numbers into the arguments. It is good when you quickly wants to sum up few numbers, you can enter directly into the arguments to return the sum value.
In the next example if you see, we have a furniture data set where we have products and their cost. Here, we need to find out the total cost of the products and we know that the cost is in continuous range in the data.
We have used SUM function in the above example but you must have noticed that the function contains only 1 argument (which is required for the function to work) that belong to the range of cells which stores numerical values. So that means we still have 254 more arguments that can be used to add more numbers.
You can also select multiple ranges. Either you can hold CTRL key while selecting multiple ranges or you can separate them by commas too.
Now what if you want to calculate a running total of the product cost. You can simply play a small trick here by using absolute reference in the function and you can get the running total of product cost alongside.
The uses of absolute reference is very powerful in Excel, especially when you have to work with dynamic or complicated formulas. As it helps to lock the cell/range references so that even if you move your formula anywhere in the spreadsheet, the references remain same.
In the above example, we have locked only first reference whereas the 2nd reference is a relative reference. So, when we dragged the formula downwards, it has moved from A2 to A3 and so on.