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.
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
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.
Select A7 to make it the active cell (the location where the results of the function will be displayed). Press the SUM button next to the input line. The SUM function should be entered into the active cell — the function should automatically enter the cell reference A6 as the number argument. To change the range of cell references used for the number argument, use the mouse pointer to highlight the range A1 to A6. Press Enter to complete the function. 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.
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.
SUM Function Example
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.
Select cell A7 to make it the active cell — the location where the results of the function will be displayed. Select the Function Wizard next to the input line (same as the formula bar in Excel) to bring up the Function Wizard dialog box. Select the Category drop-down list and select Mathematical to see the list of math functions. Under Function, select SUM from the list of functions. Select Next. Select number 1 in the dialog box, if necessary. Select cell A1 in the worksheet to enter that cell reference into the dialog box. Select number 2 in the dialog box. Select cell A3 in the worksheet to enter that cell reference. Select number 3 in the dialog box. Select cell A6 in the worksheet to enter that cell reference. Select number 4 in the dialog box. Highlight cells B2 and B3 in the worksheet to enter this range. Select OK to close the dialog box and return to the worksheet. The number 695 should appear in cell A7 — as this is the sum of the numbers located in cells A1 to B3. When you select cell A7. the complete function =SUM(A1;A3;A6;B2:B3) appears in the input line above the worksheet.
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.
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.
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.
Dialog Box Advantages
Advantages of using the dialog box include:
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. 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.
