[Calc] How to group rows and SUM?

Winfried1 · October 3, 2019, 12:39pm

Hello,

I need to group related rows, sum their values in a top-level row, and ignore the sub-level values when calling sum() at the bottom of the sheet.

I read about “Data > Group and Outline > Group”, and then pivot tables, but I’m stuck. What is the right solution to do this?

Thank you.

!https://i.ibb.co/kytCj57/D37-EC243-DC74-4-F00-9-A5-E-67-B559661578.png

gregors15 · October 3, 2019, 2:08pm

Hi, can you upload a sample file, around 20 records, sounds like a pivot table could possibly do what you want.

Winfried1 · October 3, 2019, 2:47pm

Thanks. As shown in the screenshot, I just typed a couple of rows with key/value tuples to investigate.

As a work-around, I could move the “sub-level rows” out of the way, add a top-level row to act as agregate, and type a SUM()… but I’d be surprised in there weren’t a smarter and more elegant solution.

!https://i.ibb.co/jVQCs10/9-C82-FC80-0931-4-A8-E-9616-458-C3-DC57-B12.png

anon73440385 · October 3, 2019, 3:47pm

May be you should have a look into function SUBTOTAL() and Function index 9 or 109.

gregors15 · October 3, 2019, 3:52pm

Hi, have a look at the attached, I created some items gave them a group and produced the pivot table. To produce the pivot table, I just high lighted A1 to C40, then Data/Pivot Table/New, and in the pivot table layout box dragged the Group and Material to the Row Fields box, and count to the Data fields box, selected options and ticked add filter. You can then just select the groups you want to see in the pivot table.
Let us know if it helps.LOQ_2019103A.ods