# Conditional evaluation of org mode tables?

Let's say I have a column of switches, and a column of numbers. How can I get a total of the numbers that have certain switches?

``````| Number | Switch |
|--------|--------|
|   1    | Yes    |
|   2    | Yes    |
|   9    | No     |
|--------|--------|
|=some formula here == 3 |
``````

# Table Formula Employing Lisp

The following table formula uses a lisp expression.

``````|   | Number | Switch |
|---+--------+--------|
| # |      1 | Yes    |
| # |      2 | Yes    |
| # |      9 | No     |
|---+--------+--------|
| # |      3 |        |
| ^ |  total |        |
#+TBLFM: \$total='(apply #'+ (cl-mapcar (lambda (num sw) (if (eq sw 'Yes) num 0)) '(@I\$2..@II\$2) '(@I\$3..@II\$3)));L
``````

The `cl-mapcar` applies the binary `lambda`-operator to the elements of the two lists. In the first `lambda`-call the first element of the first list is the first argument of the `lambda` and the first element of the second list is the second argument of the `lambda` and so on. The first list contains the numbers to be added and the second list contains the conditions.

# Table Formula Employing Calc

The following table formula uses pure calc expressions.

``````|   | Number | Switch |
|---+--------+--------|
|   |      1 | Yes    |
|   |      2 | Yes    |
|   |      9 | No     |
|---+--------+--------|
| # |      3 |        |
| ^ |  total |        |
#+TBLFM: \$total=evalv(vsum(if(subst(subst(@I\$3..@II\$3,Yes,1),No,0), @I\$2..@II\$2, cvec(0,vlen(@I\$2..@II\$2)))));L
``````

The `L` flag at the end of the formula means that all fields are taken literally. The expression `@I\$3..@II\$3` expands to a vector with symbols `Yes` and `No` as entries. These symbols are substituted by the `subst` operators into `1` and `0`. Thereafter the `if` operator is applied to the vector `[1,1,0]` as first argument that results from the substitutions in `[Yes,Yes,No]`. If a vector entry is 1 `if` outputs the corresponding entry of the second argument in this slot. If the entry is 0 the third argument is returned in this slot. The third argument is just a zero vector constructed by `cvec(0,vlen(@I\$2..@II\$2))`.

You may use the orgtbl-aggregate package available on Melpa.

``````#+name: mytable
| Number | Switch |
|--------|--------|
|   1    | Yes    |
|   2    | Yes    |
|   9    | No     |

#+BEGIN: aggregate :table "mytable" :cols "sum(Number)" :cond (equal Switch "Yes")
| sum(Number) |
|-------------|
|           3 |
#+END:
``````