Ask Your Question
0

[Calc] How to group rows and SUM?

asked 2019-10-03 14:39:19 +0200

Winfried gravatar image

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

edit retag flag offensive close merge delete

Comments

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

gregors15 gravatar imagegregors15 ( 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.

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

Winfried gravatar imageWinfried ( 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.

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

1 Answer

Sort by » oldest newest most voted
0

answered 2019-10-03 17:52:32 +0200

gregors15 gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-03 14:39:19 +0200

Seen: 1,099 times

Last updated: Oct 03 '19