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

Add up Columns or Rows of Numbers in Open Office Calc

young accountant counting financial figures on office desk

Towfiqu Photography / Getty Images 

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.

Instructions in this article applies to OpenOffice Calc v. 4.1.6.

OpenOffice Calc SUM Function

Two ways of entering this function include:

  • 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).
  • Adding the SUM function to a worksheet using the function wizard dialog box. The dialog box can be opened by selecting the Function Wizard 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

A maximum of 30 numbers can be added by the function.

What the SUM Function Ignores

A number left aligned in OpenOffice Calc.

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, entering

=SUM(A1:A6)

into cell A7 and pressing Enter 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 use 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 below.

  1. Select A7 to make it the active cell (the location where the results of the function will be displayed).

    The cell A7 is selecting in OpenOffice Calc.
  2. Press the SUM button next to the input line.

    The Sum button is highlighted in OpenOffice Calc.
  3. The SUM function should be entered into the active cell — the function should automatically enter the cell reference A6 as the number argument.

    Building a SUM formula with cell A6 highlighted in OpenOffice Calc.
  4. To change the range of cell references used for the number argument, use the mouse pointer to highlight the range A1 to A6.

    Cell A1 to A6 is selected in OpenOffice Calc.
  5. Press Enter to complete the function.

    The summed total is completed in OpenOffice Calc.
  6. The answer 577 should be displayed in the cell A7. When you select cell A7, the complete function = SUM (A1 : A6) appears in the input line above the worksheet.

Add Numbers Using Calc's SUM Function Dialog Box

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

  • Selecting the Function Wizard on the input line above the worksheet.
  • Pressing Ctrl + F2.

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 involves 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

A7 is selected in OpenOffice Calc.

Below are listed the steps used to enter the SUM function into cell A7 as shown in the image in step 15 . 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. Select cell A7 to make it the active cell — the location where the results of the function will be displayed.

    A7 is selected in OpenOffice Calc.
  2. Select the Function Wizard next to the input line (same as the formula bar in Excel) to bring up the Function Wizard dialog box.

    The Function Wizard button is highlighted in OpenOffice Calc.
  3. Select the Category drop-down list and select Mathematical to see the list of math functions.

    Selecting the Mathematical category in OpenOffice Calc.
  4. Under Function, select SUM from the list of functions.

    Selecting the SUM function in OpenOffice Calc.
  5. Select Next.

  6. Select number 1 in the dialog box, if necessary.

    Selecting the number 1 cell in OpenOffice Calc.
  7. Select cell A1 in the worksheet to enter that cell reference into the dialog box.

    Selecting A1 cell in OpenOffice Calc.
  8. Select number 2 in the dialog box.

    Selecting number 2 in the SUM function in OpenOffice Calc.
  9. Select cell A3 in the worksheet to enter that cell reference.

    Selecting A3 cell in OpenOffice Calc.
  10. Select number 3 in the dialog box.

    Selecting number 3 in OpenOffice Calc.
  11. Select cell A6 in the worksheet to enter that cell reference.

    Selecting cell A6 in OpenOffice Calc.
  12. Select number 4 in the dialog box.

    Selecting number 4 cell in OpenOffice Calc.
  13. Highlight cells B2 and B3 in the worksheet to enter this range.

    Highlighting cell B2 and B3 in OpenOffice Calc.
  14. Select 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.

    The result of the SUM function in OpenOffice Calc.
  16. When you select cell A7. the complete function =SUM(A1;A3;A6;B2:B3) appears in the input line above the worksheet.