Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

Suppose I have a spreadsheet in which I track my fruit purchases. It might look like this:

Date        Item    Amount
1-Apr-2012  Apple   .35
2-Apr-2012  Pear    .40
3-Apr-2012  Apple   .36

I want a summary range that looks like this:

Apple     .71
Pear      .40

In other words, I've picked unique "Item" values and summed over them. This seems like something a spreadsheet ought to be able to do, but I've not been able to find it in the gnumeric manual nor googling. (And maybe the answer is the python plugin.)

(I originally tagged this openoffice, as there was no gnumeric tag. I suspect there's a way to do this in ooo iff there's a way in gnumeric. Thanks to the kind soul who created a gnumeric tag and retagged!)

share|improve this question
    
Don't know anything about gnumeric, but in Calc (OpenOffice or LibreOffice), there's the subtotal function found in the menu under Data. –  user25656 Apr 26 '12 at 13:06
    
Oh, that's nifty, thanks, that's almost it! But it's not quite what I want, because it modifies the original range (when called from the menu) or requires contiguous ranges (grouped by). But I want the original range to remain sorted by date and, in another sheet in my workbook, I want to see a summary by item, dynamic as more items get added to (or changed in) the original range. –  jma Apr 26 '12 at 14:48
add comment

1 Answer

up vote 1 down vote accepted

In general

In general, this would be a typical task for a pivot table. Starting with the database table shown below, you would define a pivot table to sum the distinct items as follows:

pivot definition

This will give you the following result table:

pivot result

Using Gnumeric

Sadly, Gnumeric doesn't support pivot tables yet - so i used LibreOffice to create the screenshots above.

With gnumeric, you could use the DSUM function instead. It will sum the amounts for a given item, but it has the disadvantage that it won't calculate the sums for all items automatically; instead, you'll have to insert DSUM functions (and criteria tables) for every distinct item. So, it's quite cumbersome to use this functionality. Here's an example for a gnumeric sheet using the DSUM function:

gnumeric sheet

Cell F2 holds the formula =dsum(A2:C6;3;A9:C10): A2:C6 is the database (on the screenshot: in light green), 3 is the column to sum up ("Amount"), and A9:C10 is the criteria table (light cyan), selecting only rows with item "Apple".

Correspondingly, F3 has =dsum(A2:C6;3;A13:C14): almost the same, only the second criteria table instead of the first.

share|improve this answer
    
That works, and thanks for the clear and detailed explanation. I made an auxiliary sheet ('criteria') in the workbook to store all the ranges and such and made all the item names be pointers to item names in the criteria page to avoid errors from typos spelling an item ('peear', oops). Many thanks! –  jma Apr 30 '12 at 6:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.