Re: Org Mode spreadsheet SUMIF

[William Henney <whenney@gmail.com>]

Hi Jeff

On Mon, Mar 8, 2010 at 6:54 AM, Jeff Kowalczyk <jtk@yahoo.com> wrote:
> What is the Org Mode spreadsheet formula idiom for a SUMIF function?
>
> The objective is to add up the numeric values for rows matching a tag column:
>
> | 2010-03-01 | 12.2 | foo |
> | 2010-03-02 | 11.5 | foo |
> | 2010-03-02 | 12.6 | bar |
> | 2010-03-03 | 10.2 | foo |
>
> Need a total for foo: 33.9, and bar: 12.6, etc.

This is perhaps not the most elegant solution, but it does work.

|       date | values | tag | foo values | bar values |
|------------+--------+-----+------------+------------|
| 2010-03-01 |   12.2 | foo |       12.2 |          0 |
| 2010-03-02 |   11.5 | foo |       11.5 |          0 |
| 2010-03-02 |   12.6 | bar |          0 |       12.6 |
| 2010-03-03 |   10.2 | foo |       10.2 |          0 |
|------------+--------+-----+------------+------------|
|            |        |     |       33.9 |       12.6 |
  #+TBLFM: $4='(if (string-equal "$3" "foo") $2 0);L::$5='(if
(string-equal "$3" "bar") $2
0);L::@6$4=vsum(@I..@II)::@6$5=vsum(@I..@II)

The idea is to make an extra column containing only the values with a
certain tag, and then sum that. It has the advantage that there is a
natural place in the table to put each conditional sum.

You could probably avoid the need for the extra columns if you recoded
the summation using an elisp formula instead of a calc formula. As far
as I know, you can't do string comparisons inside a calc formula.

Cheers

Will

-- 

  Dr William Henney, Centro de Radioastronomía y Astrofísica,
  Universidad Nacional Autónoma de México, Campus Morelia