From mboxrd@z Thu Jan  1 00:00:00 1970
From: William Henney <whenney@gmail.com>
Subject: Re: Org Mode spreadsheet SUMIF
Date: Mon, 8 Mar 2010 09:59:03 -0600
Message-ID: <41c818191003080759p2d823e5cqdf353941f1751363@mail.gmail.com>
References: <loom.20100308T134348-271@post.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Return-path: <emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org>
Received: from mailman by lists.gnu.org with tmda-scanned (Exim 4.43)
	id 1NofMK-0008Vf-Pb
	for emacs-orgmode@gnu.org; Mon, 08 Mar 2010 10:59:08 -0500
Received: from [140.186.70.92] (port=43020 helo=eggs.gnu.org)
	by lists.gnu.org with esmtp (Exim 4.43) id 1NofMJ-0008VW-63
	for emacs-orgmode@gnu.org; Mon, 08 Mar 2010 10:59:07 -0500
Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69)
	(envelope-from <whenney@gmail.com>) id 1NofMI-0004j2-9V
	for emacs-orgmode@gnu.org; Mon, 08 Mar 2010 10:59:07 -0500
Received: from mail-fx0-f222.google.com ([209.85.220.222]:36355)
	by eggs.gnu.org with esmtp (Exim 4.69)
	(envelope-from <whenney@gmail.com>) id 1NofMI-0004io-4T
	for emacs-orgmode@gnu.org; Mon, 08 Mar 2010 10:59:06 -0500
Received: by fxm22 with SMTP id 22so2300629fxm.26
	for <emacs-orgmode@gnu.org>; Mon, 08 Mar 2010 07:59:04 -0800 (PST)
In-Reply-To: <loom.20100308T134348-271@post.gmane.org>
List-Id: "General discussions about Org-mode." <emacs-orgmode.gnu.org>
List-Unsubscribe: <http://lists.gnu.org/mailman/listinfo/emacs-orgmode>,
	<mailto:emacs-orgmode-request@gnu.org?subject=unsubscribe>
List-Archive: <http://lists.gnu.org/pipermail/emacs-orgmode>
List-Post: <mailto:emacs-orgmode@gnu.org>
List-Help: <mailto:emacs-orgmode-request@gnu.org?subject=help>
List-Subscribe: <http://lists.gnu.org/mailman/listinfo/emacs-orgmode>,
	<mailto:emacs-orgmode-request@gnu.org?subject=subscribe>
Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org
Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org
To: Jeff Kowalczyk <jtk@yahoo.com>
Cc: emacs-orgmode@gnu.org

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 col=
umn:
>
> | 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=3D'(if (string-equal "$3" "foo") $2 0);L::$5=3D'(if
(string-equal "$3" "bar") $2
0);L::@6$4=3Dvsum(@I..@II)::@6$5=3Dvsum(@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

--=20

  Dr William Henney, Centro de Radioastronom=EDa y Astrof=EDsica,
  Universidad Nacional Aut=F3noma de M=E9xico, Campus Morelia