Dynamic grouping and subtotals in Calc

[MarkRockNY]

I would like to have a spreadsheet with row grouping by date and group totals so that I can add new rows to existing groups and new groups, and the totals automatically adjusted. So far I found nothing on this. Unless I redo all subtotals every time I add new data, I can’t have the totals autoupdated.
Thanks
See example below:
First group:

Date Amount
04/01/25 20
04/01/25 30
04/01/25 40
Subtotal 90

Then I add 2nd group:

Date Amount
04/01/25 20
04/01/25 30
04/01/25 40
Subtotal 90

04/02/25 10
04/02/25 15
04/02/25 50
Subtotal 75

Then I add a new row to the 2nd group:

Date Amount
04/01/25 20
04/01/25 30
04/01/25 40
Subtotal 90

04/02/25 10
04/02/25 15
04/02/25 50
04/02/25 30
Subtotal 105

[Wanderer]

@MarkRockNY

So far I found nothing on this.

Try again?

Calc charts: automatically increasing data range

There is a workaround. First, go to Menu->Tools->Options->Libre Office Calc->General, then check the “Enlarge ranges references when adding rows/columns” checkbutton. Add a row and a column to your range and fill them whit non-numerical chars, like “-” (minus) for instance. Let call this row/column in the following as “boundary”. Define this new expanded range as range for your chart, to the “boundaries” included. When necessary, add a new row/column selecting the “boundary” row or column a…

But what I really suggest is to use a structure, which takes advantage of Calc instead of doing the work yourself:
Instead of isolated areas just use one table with
date | value
.
With a continous table you could

• add a pivot table to get your details and more
• you could filter a range to other areas to sum it there
• If you wish to have the subtotals in the table I suggest to “waste” a column for the running totals and put a formula in the third column C2
  = IF (A2=A1; C1+A2; A2) and drag this down. The problem to solve now is showing only the subtotal at the end of the day. You could use conditional formatting or use the fourth colum (remember: columns are “cheap” in Calc) with a similiar formula in D2
  = IF (A2=A3; ""; C2 ) and hide the complete column C after dragging down this formula. The decoration/inscription “Subtotal” I would put in the format of the D-column then. This will show the text, but leaves the cell numeric.

[Wanderer]

@MarkRockNY

Unless I redo all subtotals every time I add new data, I can’t have the totals autoupdated.

Calc can expand ranges in formulas, but often fails to detect your change. Assume a =SUM( A1:A10) and go to row 5. Now insert a row above or below row 5 and the range in your sum should adapt to A1:A11
.
The typical problem is: You will add something below your range, not inside. This will not cause the range to extend.
.
The thread I linked above in my comment shows a work-around: Create a boundary, wich you can include in the range and wich guides you to insert inside the range.
I would use an empty row 11 below my range A1:A10 and expand myself the sum to =SUM( A1:A11) Now make the row smaller (perhaps 1/4th of other row height) or fill it with a text like ______
When you add rows above this marker the range of the sum will expand.
Check also the setting to expand ranges mentioned in the linked thread.
.
As you still need to adapt this for every “day” in your setup, I recommend not to use this “solution” but to use a better way to organize data, as I’ve mentioned in my comment.

[MarkRockNY]

Thank you very much for your response.
1.You are correct about Libre calc properly reacting to adding new rows within the scope of the sum, for example above the last line, but not below the last line. This would be ok for existing dates with subtotals, but when I add a new date with amounts, it doesn’t seem to create a new corresponding group with a subtotal.
So for now I took a route with pivot table by date, it does what I need.
2. I’m quite familiar with tables in MS Exel, but Libre calc seems different. I looked up on tables in Libre calc and found that there is no such thing as a Table in Libre. Then I read your post again. All you suggest is a regular speadsheet with formulas for totals. This is not a Table the way I understood from using them in MS Exel. I see what you meant, but in my case, I have several columns with different numeric data for which I want to maintain daily totals. Your method would make my spreadsheet practically with 3x number of columns. So a pivot is more practical.
In any case, thanks for your suggestions.

[Villeroy]

@Wanderer

The typical problem is: You will add something below your range, not inside. This will not cause the range to extend.

Turn on “Expand references” in the general Calc options and Calc will expand references when you insert above the first row (instead of moving down the ref) and when you insert below the last row.

[Villeroy]

menu:Data>Define… comes close to “Tables”. It defines a so called “database range” which is a special kind of named range.

• A db range is always an absolute reference to a rectangle of cells. “Named ranges” can be any formula expression, literal values or absolute/relative/mixed reference.
• A db range “remembers” its last sort order and its last filter settings. menu:Data>Refresh refreshes the sort/filter after the range has been modified.
• A db range can be linked to a database query. menu:Data>Refresh refreshes this link as well.
• A db range can be used to isolate a list from other sheet contents. When you connect a database document to the spreadsheet, db ranges appear as separate tables of the pseudo-database.
• You can store simple SQL queries on db ranges in the database document and link the query result back to Calc spreadsheets (.ods) and Writer documents (.odt).