Home / ubuntu / Questions / 125317
Accepted
jma
Asked: 2012-04-27 02:56:41 +0800 CST

Sum over unique tags in a range

  • 772

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

gnumeric

1 Answers

  1. Best Answer

    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.

    • 2