It doesn’t get much simpler than this. The following formula returns the sum of all values in a range named Data:

=SUM(Data)

The SUM function can take up to 255 arguments. The following formula, for example, returns the sum of the values in five noncontiguous ranges:

=SUM(A1:A9,C1:C9,E1:E9,G1:G9,I1:I9)

You can use complete rows or columns as an argument for the SUM function. The formula that follows, for example, returns the sum of all values in column A. If this formula appears in a cell in column A, it generates a circular reference error.

=SUM(A:A)

The following formula returns the sum of all values on Sheet1 by using a range reference that consists of all rows. To avoid a circular reference error, this formula must appear on a sheet other than Sheet1.

=SUM(Sheet1!1:1048576)

The SUM function is very versatile. The arguments can be numerical values, cells, ranges, text representations of numbers (which are interpreted as values), logical values, and even embedded functions. For example, consider the following formula:

=SUM(B1,5,”6”,,SQRT(4),A1:A5,TRUE)

This odd formula, which is perfectly valid, contains all of the following types of arguments, listed here in the order of their presentation:

• A single cell reference
• A literal value
• A string that looks like a value
• A missing argument
• An expression that uses another function
• A range reference
• A logical TRUE value

The SUM function is versatile, but it’s also inconsistent when you use logical values (TRUE or FALSE). Logical values stored in cells are always treated as 0. But logical TRUE, when used as an argument in the SUM function, is treated as 1.

• Excel Tip: Have Your Cell Contents = Tab Names It's time for another edition of Stupid Excel Tricks! One of my coworkers recently asked me an Excel question.  He...
• Delete recursive files in PHP Many time when you are working with file sin php you can find this scenario: You need to erase a...
• Do You Know a NAGGER? Nagging...or a NAGIVATOR as a dear friend Janice Bernstein has coined, has got to be the most ineffective and energy...
• Excel How-to: The GETPIVOTDATA Function In a previous post, we created a simple pivot table from a list of Excel data.  Today, we'll link the...
• How Far Do Mortage Rates Have to Fall for Refinancing to Make 'Cents?' The answer is complex and involves a number of variables: current principal amount, current interest rate, closing costs, among others....