Summing groups, Aggregate costs by items in groups

Question

I have a sheet with a grouping by Feature / Subfeature / Task. Here is an example:

How can I aggregate the cost from individual tasks to subfeatures (eg. rows 147-150 of Sign-In subfeature group) and aggregate subfeature costs to feature costs (like all subfeatures of Android App)?

Variation to the first question, how can I sum up the costs of only the feature to a total cost? Or only sum up the costs of all tasks and not the grouped costs?

To my understanding it is not possible to use the QUERY function because QUERY functions can only filter based on some specific values and not on "this value is within the group" or "all values until next column with light background color". It seems pivot tables are also not working as they are requiring to have the feature and subfeature name in each row.

Of course I could go the manual route and just put SUM functions in each subfeature cell where the aggregated duration shall be place, with the selection of rows it has to aggregate until the next subfeature. But that is manual and I would like to have an automation of this.

Answer 1

There is no simple built-in way like a single function formula or tool included in the Google Sheets UI menus to do what you are asking.

One way to proceed, among many, is by using SUBTOTAL functions but you should add an additional row or use the Feature / subfeatures rows and on each of them add the corresponding ranges manually to each of them.

Another way to proceed is to transform the grouped data into a simple table (one column for Feature, one column for subfeature) then you might be able to use QUERY function, Pivot Tables as well other methods that might involve combining functions.