Single Calc Sheet: dynamically SUM by row range?

[oguruma1218]

Sample sheet uploaded below.

I want to make a simple, single sheet with a list of items that are broken up into category.

I want a separate column to show the SUMs of that category, with a sum of all the rows on top.

I can do that if I SUM(B5:B10) and SUM(B11-15), etc, but the problem is that if I have to add a row in between, I have to change the SUM logic.

Is there a way to do this dynamically? That is, have Calc adjust the SUM(B5:B10) to (B5:B11), and adjust the SUMs below it if I add a row?
by-category-list-with-sum.ods (11.7 KB)

[EarnestAl]

If you add a row between row 6 and row 12 in your sample spreadsheet (doesn’t match your description above), your Marketing total =SUM(B6:B12) will automatically update to include the new row so will read =SUM(B6:B13). The other formulas will update to reflect their new position.
If you add a row at the end of the range to be totalled, it will not be included.
The pivot table answer is of course quick and can scale. You can rearrange it to extract a total for “Gas” or any other payments you want for the whole year.

[karolus]

use Pivottables and everything you want is done by few clicks.
see Example:
by_category_with_pivottable.ods (12.0 KB)