How to Add up Columns or Rows of Numbers in Open Office Calc

Businessman working on spreadsheet on laptop

 Getty Images/Thomas Barwick

Adding up rows or columns of numbers is one of the most common operations carried out in spreadsheet programs like OpenOffice Calc. To make it easier to accomplish this task, Calc includes a built-in formula called the SUM function.

01
of 02

OpenOffice Calc SUM Function

Summing Data Using the SUM Button in Open Office Calc
Summing Data Using the SUM Button.

Ted French

Two ways of entering this function include:

  1. Using the SUM function shortcut button -- it is the Greek capital letter Sigma (Σ) located next to the input line (same as the formula bar in Excel).
  2. Adding the SUM function to a worksheet using the function wizard dialog box. The dialog box can be opened by clicking on the Function Wizard button located next to the Sigma button on the input line.

Shortcut and Dialog Box Advantages

The advantage of using the Sigma button to enter the function is that it is fast and easy to use. If the data to be summed is grouped together in a contiguous range the function will often select the range for you.

The advantage of using the SUM function dialog box is if the data to be summed is spread out over a number of non-contiguous cells. Using the dialog box in this situation makes it easier to add the individual cells to the function.

The SUM Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the SUM function is:

=SUM (number 1; number 2; ... number 30)

number 1; number 2; ... number 30 - the data to be summed by the function. The arguments can contain:

  • a list of numbers to be summed
  • a list of cell references indicating the location of the data in the worksheet
  • a range of cell references to the location of the data

Note: a maximum of 30 numbers can be added by the function.

What the SUM Function Ignores

The function ignores blank cells and text data in the selected range - including numbers that having been formatted as text.

By default, text data in Calc is left aligned in a cell -- as seen with the number 160 in cell A2 in the image above - number data aligns to the right by default.

If such text data is later converted to number data or numbers are added to blank cells in the range, the SUM function total automatically updates to include the new data.

Manually Entering the SUM Function

Yet another option for entering the function is to type it into a worksheet cell. If the cell references for the range of data to be summed is known, the function can be easily entered manually. For the example in the image above, typing

=SUM(A1:A6)

into cell A7 and pressing the Enter key on the keyboard would achieve the same result as the steps listed below for using the SUM shortcut button.

Summing Data with the SUM Button

For those who prefer to the mouse to the keyboard, the SUM button is a quick and easy way to enter the SUM function.

When entered in this fashion, the function tries to determine the range of cells to be summed based on surrounding data and automatically enters the most likely range as the function's number argument.

The function only searches for number data located in columns above or in rows to the left of the active cell and it ignores text data and blank cells.

Below are listed the steps used to enter the SUM function into cell A7 as shown in the image above.

  1. Click on cell A7 to make it the active cell (the location where the results of the function will be displayed)
  2. Press the SUM button next to the input line (as shown in the image above)
  3. The SUM function should be entered into the active cell - the function should automatically enter the cell reference A6 as the number argument
  4. To change the range of cell references used for the number argument, use the mouse pointer to highlight the range A1 to A6 
  5. Press the Enter key on the keyboard to complete the function
  6. The answer 417 should be displayed in the cell A7
  7. When you click on cell A7, the complete function = SUM (A1 : A6) appears in the input line above the worksheet.
02
of 02

Add Numbers Using Calc's SUM Function Dialog Box

Summing Data using the SUM Function Dialog Box in Open Office Calc
Summing Data using the SUM Function Dialog Box in Open Office Calc.

Ted French

As mentioned, another option for entering the SUM function is to use the function's dialog box, which can be opened either by:

  • clicking on the Function Wizard button on the input line above the worksheet.
  • pressing the Ctrl + F2 keys on the keyboard

Dialog Box Advantages

Advantages of using the dialog box include:

  1. The dialog box takes care of the function's syntax - making it easier to enter the function's arguments one at a time without having to enter the equal sign, the brackets, or the semicolons that act as separators between the arguments.
  2. When the data to be summed is not located in a contiguous range, the cell references, such A1, A3, and B2:B3 can be easily entered as separate number arguments into the dialog box using pointing -- which involve clicking on selected cells with the mouse rather than typing them in. Not only is pointing easier, it also helps to reduce errors in formulas caused by incorrect cell references.

SUM Function Example

Below are listed the steps used to enter the SUM function into cell A7 as shown in the image above. The instructions use the SUM function dialog box to enter the values located in cells A1, A3, A6, B2, and B3 as number arguments for the function.

  1. Click on cell A7 to make it the active cell -- the location where the results of the function will be displayed
  2. Click on the Function Wizard icon next to the input line (same as the formula bar in Excel) to bring up the Function Wizard dialog box
  3. Click into the Category drop-down list and select Mathematical to see the list of math functions
  4. Select SUM from the list of functions
  5. Click Next
  6. Click on the number 1 in the dialog box if necessary
  7. Click on cell A1 in the worksheet to enter that cell reference into the dialog box
  8. Click on the number 2 in the dialog box
  9. Click on cell A3 in the worksheet to enter that cell reference
  10. Click on the number 3 in the dialog box
  11. Click on cell A6 in the worksheet to enter that cell reference
  12. Click on the number 4 in the dialog box
  13. Highlight cells B2:B3 in the worksheet to enter this range
  14. Click OK to close the dialog box and return to the worksheet
  15. The number 695 should appear in cell A7 -- as this is the sum of the numbers located in cells A1 to B3
  16. When you click on cell A7 the complete function =SUM(A1;A3;A6;B2:B3) appears in the input line above the worksheet.