# [Calc] How to group rows and SUM?

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.

edit retag close merge delete

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

( 2019-10-03 16:08:24 +0200 )edit

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.

( 2019-10-03 16:47:59 +0200 )edit
1

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

( 2019-10-03 17:47:27 +0200 )edit

Sort by » oldest newest most voted

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.C:\fakepath\LOQ_2019103A.ods

more