William Henney <when...@gmail.com> writes:

> Hi Jeff
>
> On Mon, Mar 8, 2010 at 6:54 AM, Jeff Kowalczyk <j...@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.

Hi Jeff,

If there is a programming language that you're somewhat comfortable
with, then these sorts of slightly more complicated table tasks are one
of the reasons that org-babel[1] was written.

Personally I would reach for R first for a task like this:

#+tblname: table-data
| 2010-03-01 | 12.2 | foo |
| 2010-03-02 | 11.5 | foo |
| 2010-03-02 | 12.6 | bar |
| 2010-03-03 | 10.2 | foo |

#+begin_src R :var x=table-data :colnames yes
t(sapply(split(x[[2]], x[[3]]), sum))
#+end_src

#+results:
|  bar |  foo |
|------+------|
| 12.6 | 33.9 |


This was the most concise solution I came up with, plus you get the tag
names. In case you're comfortable with R, python or elisp, as a starting
point I've pasted blocks below that demonstrate the data structure that
your table is turned into in each of those languages.

Dan

[1] http://orgmode.org/worg/org-contrib/babel/index.php

*** R
#+begin_src R :var x=table-data :results output
str(x)
#+end_src

#+results:
: 'data.frame': 4 obs. of  3 variables:
:  $ V1: chr  "2010-03-01" "2010-03-02" "2010-03-02" "2010-03-03"
:  $ V2: num  12.2 11.5 12.6 10.2
:  $ V3: chr  "foo" "foo" "bar" "foo"

*** python
#+begin_src python :var x=table-data :results output
print x
#+end_src

#+results:
: [['2010-03-01', 12.199999999999999, 'foo'], ['2010-03-02', 11.5, 'foo'], 
['2010-03-02', 12.6, 'bar'], ['2010-03-03', 10.199999999999999, 'foo']]

*** elisp
#+begin_src emacs-lisp :var x=table-data :results pp
x
#+end_src

#+results:
: (("2010-03-01" 12.2 "foo")
:  ("2010-03-02" 11.5 "foo")
:  ("2010-03-02" 12.6 "bar")
:  ("2010-03-03" 10.2 "foo"))

*** python solution
#+begin_src python :var x=table-data
  [sum([row[1] if row[2] == tag else 0 for row in x]) for tag in ["bar","foo"]]
#+end_src

#+results:
| 12.6 | 33.9 |






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



_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode@gnu.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode

Reply via email to