Creating subtotals

OpenOffice.org 3 Calc Guide

Chapter 9 Data Analysis

< Previous Section

Next Section >

• Consolidating data
• Creating subtotals
• Using scenarios
• Multiple operations
• Goal seek
• Solver

< Previous Page

Next Page >

SUBTOTAL is a function listed under the Mathematical category when you use the Function Wizard (Insert > Function). Because of its usefulness, the function has a graphical interface accessible from Data > Subtotals.

As the name suggests, SUBTOTAL totals data arranged in a array—that is, a group of cells with labels for columns. Using the Subtotals dialog, you can select up to three arrays, then choose a statistical function to apply to them. When you click OK, Calc adds subtotal and grand total rows to the selected arrays, using the Result and Result2 cell styles to differentiate those entries. By default, matching items throughout your array will be gathered together as a single group above a subtotal.

To insert subtotal values into a sheet:

1. Ensure that the columns have labels.
2. Select the range of cells that you want to calculate subtotals for, and then choose Data > Subtotals.
3. In the Subtotals dialog, in the Group by list, select the column by which the subtotals need to be grouped. A subtotal will be calculated for each distinct value in this column.
4. In the Calculate subtotals for box, select the columns containing the values that you want to create subtotals for. If the contents of the selected columns change later, the subtotals are automatically recalculated.
5. In the Use function box, select the function that you want to use to calculate the subtotals.
6. Click OK.

Setting up subtotals

If you use more than one group, then you can also arrange the subtotals according to choices made on the dialog’s Options page, including ascending and descending order or using one of the predefined custom sorts defined in Tools > Options > OpenOffice.org Calc > Sort Lists.

Choosing options for subtotals