Spy Journal 3.0 Filtering the web for you

While looking at previous posts to choose a different function for this weeks post I noticed that I have never written a post specifically about the most commonly used function in Excel, the SUM function.

So today’s post remedies that. Note this post is based on Excel 2010. Most of this is still relevant in Excel 2003 and Excel 2007 is largely the same as 2010. The main difference is the ribbon references in Excel 2007/2010 vs the toolbar in 2003.

First the Official Excel Help information.

Description

The SUM function adds all the numbers that you specify as arguments. Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.

Syntax

SUM(number1,[number2],...])
The SUM function syntax has the following arguments:
number1    Required. The first number argument that you want to add.
number2,,...    Optional. Number arguments 2 to 255 that you want to add.

Remarks

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.

Accessing the SUM Function

I suspect most people never use or see this dialog box (obtained by clicking the fx link in the editing bar. however it is an easy way to see how your formula is constructed.

Most people would tend to use the auto sum feature which has been on the toolbar in Excel since at least V2 and is still in the home tab on the ribbon in Excel 2007/2010.

In both cases the drop down box allows you to select a different commonly used function.

Using the SUM formula.

The essential and normal use of the SUM formula is simply what you learnt in primer 1 maths at school. adding two numbers together for a total.

Extending that to include a range of numbers or even multiple ranges of numbers is some of the powerfulness this function contains within a spread sheet.

the easiest way to access and use the SUM formula is illustrated by simply double clicking the Sigma function symbol on the ribbon / toolbar when the cursor is positioned under a range you want to sum.

There are some very bizarre circumstances in which SUM gives incorrect results. These tend to do with the floating point calculator maths co=processor in your computer and in most cases will never be found by the casual user.

Some other references to the SUM function on this site:

http://www.spyjournal.biz/exceltips/2004/10/using-excel-status-bar-for-quick.html

http://www.spyjournal.biz/exceltips/2005/03/using-f9-key-in-formula-bar-in-excel.html