Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

conditional sum(sumif) in org-table

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?

like image 801
kitsusia Avatar asked Oct 31 '22 18:10

kitsusia


1 Answers

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))))))
like image 191
olaf b Avatar answered Nov 15 '22 17:11

olaf b