Sum over unique tags in a range

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!)

-
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

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:

This will give you the following result table:

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:

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.

-
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