2

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 | 
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)).

Other resources about this topic:

1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.