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 |
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.
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)).
Other resources about this topic:
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:
