SUM Formula in Excel - (SUM Series)

This article is part of SUM series. Know more about SUM series here.

In this SUM formula series, the first and important formula is SUM. All other SUM related formulas are derived from SUM formula only.

SUM is one of the basic function in MS Excel. May be basic, but more important in all the calculations.

What is SUM?

SUM is mathematical word. SUM means an amount or value obtained as a result of adding one or more numbers.

SUM formula in Excel?

Syntax: SUM(number1, [number2], [number3], ...)

If you see the syntax of the formula...

number1 is the number which we want to add.
number2 is the number which we want to add to the first number..
number3 is the number which we want to add to the first and second number...
and so on....

Do you observe that second argument (number2), third argument (number3) are in square brackets ([..]). The meaning is that these arguments are optional. In summary, first argument is compulsory and the other arguments are optional.

• Argument can be a constant, Cell reference, Range, an array, a formula, result of another function or defined name
• Max we can add, up to a total of 255 items.
• Logical values and text are ignored in Cells, included if typed as arguments.

Example:

If you want to add 10,20,30,40

Option # 1

With out any defined formula, you can use symbols =10+20+30+40 and it results 100

Option # 2

You may use the formula =SUM(10,20,30,40)

This is equals to =10+20+30+40 and the SUM formula results 100.

Option # 3

you can enter these numbers in different Cells. Let say, 10 in A1 Cell, 20 in C5 Cell, 30 in E1 Cell and 40 in A10 Cell

Then the formula is =SUM(A1,C5,E1,A10)

This is equals to =SUM(10,20,30,40) This is because excel replaces the Cell reference with the data.

So, Finally you get the result 100.

Option # 4

You can enter these numbers in sequence cells. Let say, you have entered from Cell A1 through Cell A4

Then the formula is =SUM(A1:A4)

Excel reads the above formula as =SUM({A1,A2,A3,A4})

As we know from Option # 2 that, excel replaces the Cell reference with the data.

Finally the result is 100

What if I want to SUBTRACT something?

There is no SUBTRACT formula in Excel. In fact, subtract formula is not required.

Let say you want to subtract 30 from 60

Still you SUM formula holds good to use =SUM(-30,60)

And the result is 30, as expected

Know about AUTOSUM option in Excel?

AUTOSUM is one of the option which is useful to give the SUM function with single click. As name suggesting, it gives SUM function AUTOmatically once you click the AUTOSUM option from menu.

You can use the AUTOSUM option to quickly SUM a range of numbers in a column or row. Click an empty cell below a column of numbers or to the right of a row of numbers, and then click AUTOSUM option. Excel selects what it determines to be the most likely range of data. Then click enter if accept the range Excel selects, or you can select your own range before clicking enter.

For Example, you have some values in Cell G1, Cell G2, Cell G5, Cell G6. If you try to use Autosum option in Cell G7, then it gives the formula as =SUM(G5:G6). This is because Autosum takes most likely range. If the range is disconnects, Autosum may not apply for that. Then you have to select your own range.

This Autosum option perfectly works for Option # 4 above.

Shortcut for Autosum is Alt+=

Autosum for group of totals

Autosum option even works for one of more ranges at a time.

Data before Autosum

Now, I want to calculate the total of each sales person and total of each region.

Select the data including the blank cells (where you want the totals) then use Autosum option shortcut (ie., Alt+= )

Data after Autosum

So, with single shortcut Excel calculates 10 totals. That is what the power of Autosum option.

If you see independently, SUM formula is so basic. If you use SUM formula with the other formulas then you will enjoy the sweetness of SUM formula.

If I missed anything here, please let me know by giving your comment here.