How can I calculate the sum in one of several growing groups in google sheets without manually adjusting all of the indexes?

Question

I'll admit ahead of time, this'd probably be easier to do in multiple sheets, but because of my uses for this application, I'd prefer to combine each group into a single sheet.

Currently I have multiple grouped sections. Below each grouped second I have a row that exists to do a sum of the prior rows. On line E14 for example, I have the following cell.

=SUM(INDIRECT("E9:E"&row()-1))

This is all well and good for a single group, because continues to sum until the line before E14.

Then I have another group below E14 and another cell at E20 written as

=SUM(INDIRECT("E15:E"&row()-1))

The problem being that I could add row below E13. The query previously calculating from E9 to E13 will automatically sum until E14 like I want, but the second query will still try to sum from E15 to E20, even though the new starter row ought to be E16.

If it helps, I have been creating groups for each piece of my data, so there's a header row at E9 and grouped rows under it from E10-E13, a header at E15 and grouped rows under it from E16-E19, etc.

Thanks!

How groups are identified ? are they sorted ? Please share a copy of your sheet. — Mike Steelson
Put some sample data and output you want. That would be better for understanding your problem. It seems you can use UNIQUE() then SUMIFS() function. — Harun24HR

Sergey Sergey · Accepted Answer · 2021-05-06T07:55:58

I can advise you to use a named range in the formula. Specify a name for your Header cell and use it in the formula =SUM(OFFSET(Header1;1;1):OFFSET(B15;-1;0)) or =SUM(INDIRECT("B"&ROW(Header1)+1&":B"&ROW()-1))

How can I calculate the sum in one of several growing groups in google sheets without manually adjusting all of the indexes?

1 Answers