• Mathematical and Financial Functions
  • Mathematical and Financial Functions
  • ABS function
  • AVERAGE function
  • CGROUP function
  • CGROUPT function
  • COLCOUNT function
  • COUNTIF function
  • EXP function
  • FV function
  • LOG function
  • MAX function
  • MEDIAN function
  • MIN function
  • MKDEC function
  • MKINT function
  • MOD function
  • POS function
  • PV function
  • R function
  • RATIO function
  • RC function
  • RCNV function
  • RCR function
  • RCTOTAL function
  • ROUND function
  • ROWCOUNT function
  • RR function
  • RRTOTAL function
  • STDDEV function
  • SUM function
  • TOTAL function

SUM Function

The SUM function calculates the sum of a range of cell values. An optional constant may be added to sum all cells in a specific increment. If no constant is supplied, an increment of one is assumed.

Syntax

SUM(cell number..cell number<,constant<,argument>>)

The constant specifies if any cells should not be included. If no value is entered, the default constant is 1 (include all).

The argument is optional and controls which cells are summed. If no argument is specified, the function sums the contents of all cells in the range.

Argument	Result
0	The function returns a count of all cells in the range.
1	The function returns a count of cells in the range that have a non-zero value.
2	The function returns the sum of all cells in the range with positive values.
3	The function returns the sum of all cells in the range with negative values.

 

A cell with a zero value is a cell that is empty or that contains a blank.

Note: If an argument is used then a constant must be specified.

Example 1

SUM(c1..c9)

Returns the sum of all cells numbered c1 to c9.

Example 2

SUM(c2..c10,2)

Returns the sum of cells numbered c2, c4, c6, c8, and c10.

Example 3

SUM(c2..c10,1,1)

Returns the count of all non-zero value cells in the range c2 to c10.

Note

• If inserting a row between the last entry in the sum calculation and sum row, the sum calculation automatically updates for the new row added. For example, if the cells look like this:

A1

A2

sum(a1..a2)

adding a row between A2 and the sum changes the sum to sum(a1..a3). However, adding a row only updates the summation if the row above the inserted row was actually included in the sum. For example, if the cells look like this:

A1

A2

A3

sum(a1..a2)

adding a row between A2 and the sum does not update the sum.

Likewise, deleting a row from a summed group of cells updates the sum as well. If the cells look like this:

A1

A2

A3

sum(a1..a3)

deleting row A3 will update the sum to sum(A1..A2)

Related Topics

• Mathematical and Financial Functions