# Conditionally calculate the sum of a column depending on a fields value in another cell of the same row

In orgmode, I do have a table like the following to track my overall working time a day. It is not important for which tasks or projects or even which client I spent my time on, just if I was in office or worked from remote.

The table looks like this:

``````| Day | Start |   End | Home |   Sum |
|-----+-------+-------+------+-------|
|   1 | 09:00 | 17:00 | No   |  8.00 |
|   2 | 09:00 | 12:00 | No   |  3.00 |
|   2 | 13:00 | 20:00 | Yes  |  7.00 |
|-----+-------+-------+------+-------|
|     |       |       | 7.00 | 11.00 |
#+TBLFM: @2\$5..@-1\$5=\$3-\$2;t
``````

Now I need a formula that calculates the fields `@<\$4` and `@<\$5` as sums, while in `@<\$4` the values of \$5 are summed when in that row \$4 is `Yes` and in `@<\$5` the values of \$5 shall be summed when \$3 of that row is not `Yes`.

Instead of `Yes`/`No` I can switch to any other kind of marker as well.

• Do any of the answers work for you? – Melioratus Sep 16 '16 at 4:38

I guess there is 2 options .. calculate some helper columns and sum multiply those or create helper elisp function .. I'll start with the latter.

``````#+BEGIN_SRC elisp
(defun my-sum-helper (a  b pred)
(reduce #'+
(cl-mapcar
#'*
(mapcar
(lambda(x) (if (string= x pred) 1 0)) a)
b)))
#+END_SRC

#+RESULTS:
: my-sum-helper

| Day | Start |   End | Home |  Sum |
|-----+-------+-------+------+------|
|   1 | 09:00 | 17:00 | No   | 8.00 |
|   2 | 09:00 | 12:00 | No   | 3.00 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 |
|-----+-------+-------+------+------|
|     |       |       | 7.0  | 11.0 |
#+TBLFM: @2\$5..@-1\$5=\$3-\$2;t::@>\$4='(my-sum-helper '(@2\$4..@4\$4) '(@2\$5..@4\$5) "Yes");L::@>\$5='(my-sum-helper '(@2\$4..@4\$4) '(@2\$5..@4\$5) "No");L
``````

To use: evaluate the src block first then evaluate the formulas.

LIke I said you can also create an intermediate column wih 0 an 1 values with the formula: `@2\$6..@4\$6='(if (string= "\$4" "Yes") 1 0);L`. This facilitates the use of calc:

``````| Day | Start |   End | Home |  Sum |   |
|-----+-------+-------+------+------+---|
|   1 | 09:00 | 17:00 | No   | 8.00 | 0 |
|   2 | 09:00 | 12:00 | No   | 3.00 | 0 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 | 1 |
|-----+-------+-------+------+------+---|
|     |       |       | 7.   |  11. |   |
#+TBLFM: @2\$5..@-1\$5=\$3-\$2;t::@2\$6..@4\$6='(if (string= "\$4" "Yes") 1 0);L::@>\$4=vsum(@2\$6..@4\$6 * @2\$5..@4\$5);N::@>\$5=vsum((1-@2\$6..@4\$6) * @2\$5..@4\$5);N
``````

hope this helps.

• The first solution with code fills both result cells with `#ERROR` on macOS 10.14.5 and Emacs 26.2 (9.0). – miguelmorin Jul 1 '19 at 8:04

### Create table function, i.e. `#+TBLFM:`, and use the built-in org-lookup-all function with string= predicate and apply elisp function.

Note: Use the Literal mode L or the cell address references may misbehave.

I've wondered how to do calculations against filtered subset of an org-mode table data for years and never figured out how until today!

``````| Day | Start |   End | Home |  Sum |
|-----+-------+-------+------+------|
|   1 | 09:00 | 17:00 | No   | 8.00 |
|   2 | 09:00 | 12:00 | No   | 3.00 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 |
|-----+-------+-------+------+------|
|     |       |       | 7.0  | 11.0 |
#+TBLFM: @2\$5..@-1\$5=\$3-\$2;t::@>\$4='(apply '+ (org-lookup-all "Yes" '(@I\$4..@II\$4) '(@I\$5..@II\$5) 'string= ));L::@>\$5='(apply '+ (org-lookup-all "No" '(@I\$4..@II\$4) '(@I\$5..@II\$5) 'string= ));L
``````

### The Tricky Bits

• Filtering the table with `org-lookup-all` function

``````(org-lookup-all VAL S-LIST R-LIST &optional PREDICATE)
``````
• `VAL` is the filter value, e.g. Yes or No.
• `S-LIST` is the search list, e.g. `("No" "No" "Yes")`.
• `R-LIST` is the returned list, e.g. `(8.00 3.00 7.00)`
• `PREDICATE` is not optional in our answer because we're comparing strings, e.g. `string=`

• Returns a filtered list, e.g. `(8.0 3.0)`

• Using `apply` elisp function instead of the `vsum` calc function.

Couldn't get `vsum` to work - blah.

This code was tested using
GNU Emacs 24.4.1 (x86_64-apple-darwin14.0.0, NS apple-appkit-1343.14)
Org-Mode Version: 8.3.5

An alternative would be to use the `orgtbl-aggregate` available on Melpa.

`````` #+name: yourtable
| Day | Start |   End | Home |   Sum |
|-----+-------+-------+------+-------|
|   1 | 09:00 | 17:00 | No   |  8.00 |
|   2 | 09:00 | 12:00 | No   |  3.00 |
|   2 | 13:00 | 20:00 | Yes  |  7.00 |

#+BEGIN: aggregate :table "yourtable" :cols "Home sum(Sum)"
| Home | sum(Sum) |
|------+----------|
| No   |      11. |
| Yes  |       7. |
#+END:
``````

See the repo for installation instructions:

``````(add-to-list 'package-archives '("melpa" . "http://melpa.milkbox.net/packages/") t)
(package-initialize)
(package-install "orgtbl-aggregate")
``````