Adding rows or columns of numbers is one of the most common operations performed in spreadsheet programs such as OpenOffice Calc. To make this task easier, Calc includes a built-in formula called the SUM function.
- OpenOffice Calc SUM Function
- Benefits of Shortcuts and Dialogs
- Syntax and arguments of the SUM function
- What the SUM function ignores
- Manual entry of the SUM function
- Add data with the SUM button
- Add numbers using the SUM function Calc dialog box
- Benefits of the dialog box
- SUM function example
OpenOffice Calc SUM Function
There are two ways to enter this function:
With the keyboard shortcut, SUM is the Greek capital Sigma (Σ) next to entry line: (similar to the formula bar in Excel).
Add the SUM function to a worksheet using the Function Wizard dialog box. This dialog can be opened by clicking the button Function Wizard next to the Sigma button in the entry line .
Benefits of Shortcuts and Dialogs
The advantage of using the Sigma button to enter a function is that it is quick and easy to use. If the data to be added is grouped in a continuous range, the function will often select the range for you.
The advantage of using the SUM function dialog box is that the data to be summed is spread over multiple non-adjacent cells. Using a dialog box in this situation makes it easier to add individual cells to a function.
Syntax and arguments of the SUM function
Function syntax refers to the layout of the function and includes the function name, parentheses, and arguments.
Syntax for the SUM function:
= SUM(number 1; number 2; … number 30)
number 1; number 2; … number 30 – data summarized by the function. Arguments may include:
list of numbers to add up
list of cell references that indicate the location of the data on the sheet
cell reference range for data location
Remark : You can add up to 30 songs with this function.
What the SUM function ignores
The function ignores blank cells and text data in the selected range, including numbers formatted as text.
By default, text data in Calc is left aligned in a cell – as you can see from the number 160 in cell A2 in the image above – numeric data is right aligned by default.
If such text data is later converted to numeric data, or if numbers are added to empty cells in a range, the SUM function’s total value is automatically updated with the new data.
Manual entry of the SUM function
Another way to enter a function is to enter it into a worksheet cell. If the cell references for the range of data to be summarized are known, the function can easily be entered manually. For the example in the image above, type
to cell A7 and press the key Enter on the keyboard results in the same result as the steps below for using the SUM hotkey.
Add data with the SUM button
For those who prefer a mouse over a keyboard, the SUM button is a quick and easy way to enter the SUM function.
When entered this way, the function tries to determine the range of cells to sum based on the surrounding data and automatically enters the most likely range as argument number functions.
The function only searches for numeric data in the columns above or in the rows to the left of the active cell and ignores text data and empty cells.
Below are the steps used to enter the SUM function in cell A7, as shown in the image above.
Click on cell A7 to make it the active cell (the place where the results of the function are displayed)
Press the SUM button next to the entry line (as shown in the image above)
The SUM function must be entered in the active cell – the function must automatically enter a reference to cell A6 as an argument number
To change the range of cell references used for an argument: number use the mouse pointer to highlight the range from A1 to A6
Press key Enter on the keyboard to complete the function
Response 417 should appear in cell A7
If you click on cell A7, the entire function =SUM(A1:A6) will appear in entry line: above the worksheet.
Add numbers using the SUM function Calc dialog box
As mentioned before, another option to enter the SUM function is to use the function’s dialog box, which can be opened in one of the following ways:
Press a button Function Wizard on the entry line: above the worksheet.
by pressing the Ctrl + F2 keys on the keyboard
Benefits of the dialog box
The benefits of using a dialog box include:
The dialog takes care of the function’s syntax – making it easy to enter the function’s arguments one at a time without typing an equal sign, parentheses, or semicolons that act as separators between the arguments.
If the data to be summed is not in an adjacent range, cell references such as A1, A3, and B2:B3 can simply be entered as separate arguments number to the dialog box using the pointer – clicking the mouse on the selected cells instead of typing them. Not only does this make hovering easier, but it also helps reduce formula errors caused by incorrect cell references.
SUM function example
Below are the steps used to enter the SUM function in cell A7, as shown in the image above. The instructions use the SUM Function dialog box to enter the values in cells A1, A3, A6, B2, and B3 as numeric arguments to the function.
Click on cell A7 to make it the active cell – the place where the results of the function are displayed
Click on icon Function Wizard Near entry line: (similar to the formula bar in Excel) to open the Function Wizard dialog
Click the Category drop-down list and select Mathematics to see a list of math functions.
Select SUM from the list of functions
If necessary, click number 1 in the dialog box.
Click cell A1 on the worksheet to enter a reference to that cell in the dialog box.
Press number 2 in the dialog box.
Click on cell A3 on the sheet to enter a link to that cell
Press number 3 in the dialog box.
Click on cell A6 on the worksheet to enter a link to that cell
Press number 4 in the dialog box.
Highlight cells B2:B3 on the worksheet to enter this range
Click OK to close the dialog box and return to the worksheet.
Number 695 should appear in cell A7 because it is the sum of the numbers in cells A1 through B3.
When you click on cell A7, the entire function appears in the input bar above the worksheet =SUM(A1;A3;A6;B2:B3) .