I have a table like this:
#+NAME: ENTRY
|------+--------|
| Item | Amount |
|------+--------|
| A | 100 |
| B | 20 |
| A | 120 |
| C | 40 |
| B | 50 |
| A | 20 |
| C | 16 |
|------+--------|
and then I need to sum each item in another table:
#+NAME: RESULT
|------+-----|
| Item | Sum |
|------+-----|
| A | 240 |
| B | 70 |
| C | 56 |
|------+-----|
I've tried using vlookup and remote reference in this table, but I'm not able to sum the resulting list like:
#+TBLFM: $2=vsum((vconcat (org-lookup-all $1 '(remote(ENTRY,@2$1..@>$1)) '(remote(ENTRY,@2$2..@>$2)))))
But it does not give the answer.
So I have to use a place holder to hold the resulting list then sum it:
#+NAME: RESULT
|------+--------------+-----|
| Item | Placeholder | Sum |
|------+--------------+-----|
| A | [100 120 20] | 240 |
| B | [20 50] | 70 |
| C | [40 16] | 56 |
|------+--------------+-----|
#+TBLFM: $2='(vconcat (org-lookup-all $1 '(remote(ENTRY,@2$1..@>$1)) '(remote(ENTRY,@2$2..@>$2))))::$3=vsum($2)
Is there a better solution for this?
One way to do this is without vsum:
#+TBLFM: $2='(apply '+ (mapcar 'string-to-number (org-lookup-all $1 '(remote(ENTRY,@2$1..@>$1)) '(remote(ENTRY,@2$2..@>$2)))))
If you want to use a calc function, you can always use calc-eval:
#+TBLFM: $2='(calc-eval (format "vsum(%s)" (vconcat (org-lookup-all $1 '(remote(ENTRY,@2$1..@>$1)) '(remote(ENTRY,@2$2..@>$2))))))