Ask Your Question
1

How can I calculate aggregate sums in Calc? [closed]

asked 2017-09-29 23:25:08 +0100

stanton gravatar image

updated 2020-10-16 15:03:57 +0100

Alex Kemp gravatar image

Assume I have a table like the following:

Mammal | Cow      | 17
Mammal | Horse    |  5
Mammal | Pig      | 11
Bird   | Duck     | 12
Bird   | Goose    |  8
Fish   | Sturgeon |  3
Fish   | Carp     | 14
Fish   | Trout    | 26

I would like to calculate sums across the third column, based on the value of the first. Like:

Mammal | 33
Bird   | 20
Fish   | 40

In a SQL database I would just do

SELECT A, SUM(C) FROM table GROUP BY A;

How can I automate this in Calc? Auto-update (sum changes as soon as I change anything in the first table) would be nice, but is not mandatory.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-16 15:04:07.488353

1 Answer

Sort by » oldest newest most voted
2

answered 2017-09-30 05:06:13 +0100

Jim K gravatar image

updated 2017-09-30 05:10:37 +0100

Use a Pivot Table.

To do this, insert a new first row. Enter "Type" in A1, "Animal" in B1, and "Count" in C1. Then select cells A1 through C9, go to Data -> Pivot Table -> Create, and press OK.

In the Pivot Table Layout dialog, drag "Type" to Row Fields, and "Count" to Data Fields. Then press OK.

Type            Sum - Count
~~~~            ~~~~~~~~~~~
Bird            20
Fish            43
Mammal          33
Total Result    96

To update, right-click on the pivot table and choose Refresh.

edit flag offensive delete link more

Comments

Sounds good, except that the menu item you describe is missing. Under Data > Pivot Table, I only see Refresh and Delete—the Create item is not there. LO 5.1.6.2 on Ubuntu MATE 16.04 LTS.

stanton gravatar imagestanton ( 2017-09-30 17:14:22 +0100 )edit

Got it. It’s under Insert > Pivot Table in later versions, albeit even Help still points to the old location.

stanton gravatar imagestanton ( 2017-09-30 17:15:53 +0100 )edit

My instructions were made using LO 5.4.0.3, so it would seem that the newer location is the Data menu. Alas, the menu changes frequently between releases.

Jim K gravatar imageJim K ( 2017-09-30 17:56:39 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-09-29 23:25:08 +0100

Seen: 3,072 times

Last updated: Sep 30 '17