Using the OpenOffice Calc Subtotal Feature

You have many ways to add up figures in spreadsheets. The Quick Sum feature on the toolbar, for instance. Click below or to the right of a column or row of numbers, and click the Quick Sum icon.

Then the proposed total appears, and you can just press Enter or click the green checkmark icon if you want it.

However, sometimes things are a little more complicated? What if you had something like this, with groups of figures you want to subtotal? You'd want subtotals as well as a total. If you put in the subtotals with the Quick Sum feature, then you'd have to make sure that your Total figure included only the original cells, not the subtotal amounts, as well.

A situation like this is when you'd use the Subtotal feature.

Select the data and the headings; selecting the headings makes it easier to choose the data to work with.

Choose Data > Subtotals.

In the Subtotals window, in the first tab, select the columns you want to add up, and be sure that Sum is marked as shown.

If you don't want to add, but do averages or something, you can do that, as well.  Select the function you want in the Use Function list.

Click the Options button and be sure that the Pre-Sort Area According to Groups option is unmarked. Marking it will reorganize the data, and not in a good way.

Click OK.

The subtotals will appear; here are the results with Sum as the function.

Here's an example of the results with Average chosen as the function instead of Sum.

Look at the left-hand border of the spreadsheet; you'll see little + signs and brackets. Click each to determine which of the data you see; you can display everything for each group of data, or just the total.

Comments

[Wan]

Good day,

I facing problem when using IF function in Ooo compare to Excell.

Excel: From this formula if A1 is blank, the cell will be blank also and if A1 have value or word the cell will be OK.

IF(A1=" "," ","OK")

Ooo : Same formula key-in but the result become #N/A

How to set formula in Calc, IF the A1 empty and I want the cell will be empty also.

Please help me..... Sad

[Eduardo Neto]

Is it possible to use more than 3 levels for subtotals. I know it is possible to do it manually, by using the Data->Outline->Group. But this is very tedious and time consuming for a large spreadsheet. Would be nice to have the 4th,5th,nth grouping options.

[Francesco Minciotti]

Thank you so much for this useful walkthrough.
Could sound odd to you, but I spent ten minutes staring at the Calc screen wondering about how to set the simple "sum" task work, then I found you and your guide :)

[Matthew]

Hello,
When calculating "cost per unit" sometimes it is less than $o.oo and I want it to print in cell as $0.01 ?
I am using this formula: =IF(C83="";"";(IF(B83/C83="<0.00";"0.01";(SUM(B83/C83)))))
which works as long as the correct answer is $0.005 or greator? thats more like rounding and not my formula, I don't undertand?
In "options" or "format cell" it is set to 2 decimal places.
I have tried changing the variable for the "Less Than" and it has no effect...
It will only print out $0.01 when the calc'ed answer is $0.005 or more...
help?

[Solveig]

Hi Matthew,

I'm afraid I can't help you much, it sounds like internal programming.

[Robert]

when i use the subtotal function, calc not only inserts little + -signs brackets and 123 buttons on the left-hand border of the spreadsheet, but also another set on the top of the sheet. That is not so bad and even may be useful. The problem is when I remove the subtotal, and the abc/+- handles on the left disappear fine, BUT, the ones on the top remain. If I preform another subtotal, the group on the top expends to 12345 etc taking up more and more space. I have found no way to get rid of them unless I undo everything until I am back before I started the subtotal use. Another way I have found to remove these is to open the sheet in Excel and remove all subtotals. Do you know if there is a way to remove these pesky numbers in calc? I am using version 3.01.