How to use Sum Function in Microsoft Excel

In Excel SUM function 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.

Use of Sum Function     

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.

 Special Notes

·         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

Examples – SUM Function

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.

Example - Sum function

Example – Sum function

We have used SUM function in the above example but you must have noticed that the function contains only 1 argument. It belongs to the range of cells which stores numerical values. Which means still we 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.

Sum Multiple Columns - Excel

Sum Multiple Columns – Excel

Now what if you want to calculate a running total of the product cost. You can get the running total of product cost alongside by using absolute reference in the function.

Excel Sum Formula Multiply

Multiply using Excel Sum Formula

The uses of absolute reference is very powerful in Excel, especially when you have to work with dynamic or complicated formulas. It helps to lock the cell/range references so even if you move your formula anywhere in spreadsheet, references remain same.

In the above example, we have locked only first reference whereas the 2nd reference is a relative reference. So, when we drag the formula downwards, it has moved from A2 to A3 and so on.

Related Articles


More Examples of Sum Function