0

I have a sheet with a grouping by Feature / Subfeature / Task. Here is an example: Google sheet 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.

2
  • Welcome to Web Applications Please only one question per post (ref. How to Ask). There are several way do what you are looking for i.e. using QUERY function, Pivot Tables, combining functions, ... please show what you tried and add a brief description of your search efforts as is suggested in the previous link. Jan 21, 2022 at 4:53
  • Hi Rubén thanks for giving advise. In fact the second question is just a variation of the first question. I always have problems to write an easy to understand question but I added some sentences to explain what I considered to achieve my goal and why I failed.
    – velop
    Jan 22, 2022 at 23:28

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.