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
