0

Windows 10, Emacs 26.1

here my org table:

|   | Start date       | End date         | Task     | State     | Duration (hours) |
|---+------------------+------------------+----------+-----------+------------------|
| # | 15.08.2019 10:30 | 15.08.2019 12:00 | T-320    | Taken     |             1.50 |
| # | 15.08.2019 13:00 | 15.08.2019 14:00 | T-320    | Taken     |             1.00 |
| # | 15.08.2019 14:30 | 15.08.2019 18:00 | T-321    | Taken     |             3.50 |
| # | 16.08.2019 10:30 | 16.08.2019 12:00 | T-321    | Taken     |             1.50 |
| # | 16.08.2019 13:30 | 16.08.2019 15:30 | T-321    | Taken     |             2.00 |
| # | 16.08.2019 15:30 | 16.08.2019 18:00 | T-322    | Taken     |             2.50 |
| # | 28.08.2019 14:00 | 28.08.2019 17:00 | T-330    | Taken     |             3.00 |
| # | 28.08.2019 17:00 | 28.08.2019 18:00 | T-331    | Taken     |             1.00 |
| # | 29.08.2019 10:30 | 29.08.2019 15:00 | INIT-117 | Done      |             4.50 |
| # | 29.08.2019 15:30 | 29.08.2019 16:00 | FM-222   | Done      |             0.50 |
| # | 29.08.2019 16:00 | 29.08.2019 18:00 | T-331    | Postponed |             2.00 |
| # | 30.08.2019 10:30 | 30.08.2019 12:00 | INIT-121 | Taken     |             1.50 |
| # | 30.08.2019 13:30 | 30.08.2019 21:00 | INIT-121 | Taken     |             7.50 |
|---+------------------+------------------+----------+-----------+------------------|
|   |                  |                  |          |           |            32.00 |
#+TBLFM: $6=$3-$2;t::@>$6=vsum(@I..@II);%.2f 
#+TBLFM: $6=$3-$2;t::@>$6=vsum(@557..@564);%.2f  

When I want to calculate sum of all rows in column Duration I use M-x org-recalculate-table

The result is 32.00 Nice it's work fine.

But suppose I want to calculate sum only 1, 5, 7 rows. And I want do this WITHOUT delete rest rows. The result must be 6.50

|   | Start date       | End date         | Task  | State | Duration (hours) |
|---+------------------+------------------+-------+-------+------------------|
| # | 15.08.2019 10:30 | 15.08.2019 12:00 | T-320 | Taken |             1.50 |
| # | 16.08.2019 13:30 | 16.08.2019 15:30 | T-321 | Taken |             2.00 |
| # | 28.08.2019 14:00 | 28.08.2019 17:00 | T-330 | Taken |             3.00 |
|---+------------------+------------------+-------+-------+------------------|
|   |                  |                  |       |       |             6.50 |
#+TBLFM: $6=$3-$2;t::@>$6=vsum(@I..@II);%.2f 
#+TBLFM: $6=$3-$2;t::@>$6=vsum(@557..@564);%.2f  

How I can do this without remove rows?

1

You may want to try the orgtbl-aggregate package available on Melpa. You will be able to compute partial sums of cells in a column depending on another column. Without changing your table.

For example you may aggregate rows with similar State values:

#+BEGIN: aggregate :table "atable" :cols "State vsum('Duration (hours)')"
| State     | vsum('Duration (hours)') |
|-----------+--------------------------|
| Taken     |                      25. |
| Done      |                       5. |
| Postponed |                       2. |
#+END:

If you want the global sum, just remove State in the aggregation specification:

#+BEGIN: aggregate :table "atable" :cols "vsum('Duration (hours)')"
| vsum('Duration (hours)') |
|--------------------------|
|                      32. |
#+END:

In your example, to select just rows 1,5,7 you would add a column to flag those rows:

#+name: atable
|   | Start date       | End date         | Task  | State | Duration (hours) | flag |
|---+------------------+------------------+-------+-------+------------------+------|
| # | 15.08.2019 10:30 | 15.08.2019 12:00 | T-320 | Taken |             1.50 | YES  |
| # | 15.08.2019 13:00 | 15.08.2019 14:00 | T-320 | Taken |             1.00 | no   |
...etc...

#+BEGIN: aggregate :table "atable" :cols "flag vsum('Duration (hours)')"
| flag | vsum('Duration (hours)') |
|------+--------------------------|
| YES  |                      6.5 |
| no   |                     25.5 |
#+END:

https://github.com/tbanel/orgaggregate/blob/master/README.org

3
  • Is it possible to sum value in column "Duration" in all rows contain some text (e.g "T-321")? I don't want to add additional column like "flag" – a_subscriber Sep 6 '20 at 18:34
  • This help me: aggregate :table "atable" :cols "Task vsum(Duration);f2" :cond(string= Task "T-321") – a_subscriber Sep 6 '20 at 20:30
  • 1
    Right! With :cond you can add filtering on the input rows. Only rows for which the custom lisp expression return non-nil will be considered by Aggregate – tbanel Sep 8 '20 at 6:41
2

You can use the calc command subscr. It also works for sets of indices as the following eval in the *scratch* buffer shows:

(calc-eval "subscr({1.1,2.2,3.3,4.4,5.5,6.6,7.7,8.8,9.9},{1,5,7})")
"{ 1.1,
  5.5,
  7.7 }"

Your table with the modified formula looks as follows.

|   | Start date       | End date         | Task     | State     | Duration (hours) |
|---+------------------+------------------+----------+-----------+------------------|
| # | 15.08.2019 10:30 | 15.08.2019 12:00 | T-320    | Taken     |             1.50 |
| # | 15.08.2019 13:00 | 15.08.2019 14:00 | T-320    | Taken     |             1.00 |
| # | 15.08.2019 14:30 | 15.08.2019 18:00 | T-321    | Taken     |             3.50 |
| # | 16.08.2019 10:30 | 16.08.2019 12:00 | T-321    | Taken     |             1.50 |
| # | 16.08.2019 13:30 | 16.08.2019 15:30 | T-321    | Taken     |             2.00 |
| # | 16.08.2019 15:30 | 16.08.2019 18:00 | T-322    | Taken     |             2.50 |
| # | 28.08.2019 14:00 | 28.08.2019 17:00 | T-330    | Taken     |             3.00 |
| # | 28.08.2019 17:00 | 28.08.2019 18:00 | T-331    | Taken     |             1.00 |
| # | 29.08.2019 10:30 | 29.08.2019 15:00 | INIT-117 | Done      |             4.50 |
| # | 29.08.2019 15:30 | 29.08.2019 16:00 | FM-222   | Done      |             0.50 |
| # | 29.08.2019 16:00 | 29.08.2019 18:00 | T-331    | Postponed |             2.00 |
| # | 30.08.2019 10:30 | 30.08.2019 12:00 | INIT-121 | Taken     |             1.50 |
| # | 30.08.2019 13:30 | 30.08.2019 21:00 | INIT-121 | Taken     |             7.50 |
|---+------------------+------------------+----------+-----------+------------------|
|   |                  |                  |          |           |              6.5 |
#+TBLFM: @>$6=vsum(subscr(@I$6..@II$6,{1,5,7}));%.2f
2
  • This formula also work: #+TBLFM: $6=$3-$2;t::@>$6=vsum(subscr(@I$6..@II$6,{1,5,7}));%.2f – a_subscriber Sep 5 '19 at 15:57
  • @a_subscriber Ah, yes, I forgot to put the format specifier back. Corrected right now. Thanks for the notification. – Tobias Sep 5 '19 at 21:13

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.