When you collapse a group, actually what happens is the rows grouped under it go hidden. Do you know how to get the total of values from the hidden rows that also on the group header row? In this post, I am explaining how to make the total appear on the group header only when the group/subgroup is collapsed in Google Sheets.

I know an example (screenshot) is necessary at this juncture and that will help me also to explain further.

Let’s understand it better.

Here there are two main tasks (parent) – “Task 1” and “Task 2”.

There are subtasks (child) “a”, “b”, “c”, “d”, “e”, and “f” grouped under “Task 1”. Also, the subtasks “c” and “f” have their own subtasks grouped under them.

When I collapse the subgroup “c”, I want the total cost of its subtasks to appear in cell B6 which is in the header row of the collapsed subgroup “c”.

Similarly, if I collapse the group “Task 1”, total to be appeared on the group header row (cell B3).

If you find it a little difficult to understand, experiment it yourself first and come back. Here is the sample sheet.

Collapse Groups – Sample

How to get the total only when group or subgroup is collapsed as above in Google Sheets?

From my shared sheet, you could already lay on your hand on my formulas. Here are those formulas step-by-step and their explanations.

If you are new to grouping rows and columns read this post first – How to Group Rows and Columns in Google Sheets.

How to Find a Group or Subgroup Is Collapsed or Not in Google Sheets?

Without knowing a group/subgroup is collapsed or not, you can’t write a formula to sum collapsed group in Google Sheets.

The SUBTOTAL is the only function in Google Sheets (also in Excel) to find whether a group is collapsed or expanded. Let’s see how?

Ref.: Google Sheets Function Guide.

We want to total the column B (as per my sample dataset) based on collapsed groups. So you should ensure one thing that there are no blank cells in the data range (B3:B19) in column B. You should at least enter 0 in blank cells or leave white space, which suits best to you.

The exception to this is the cells B3, B6, B12, and B16 which may contain formulas (group/subgroup header rows).

Formula to Find Whether a Group | Subgroup Is Collapsed or Expanded

Let’s take the group “Task 1” for example purpose. It covers the rows 4 to 15 in the grouping, right?

The first cell in column B (sum column) in this group is cell B4. Using the below formula we can find whether the group “Task 1” is collapsed or expanded.

=if(subtotal(103,B4)=1,"Expanded","Collapsed")

Insert this formula in cell E3. Then collapse and expand the group “Task 1”. The formula would return the string “Expanded” or “Collapsed” based on your action”

In short, if subtotal(103,B4) returns 1, the group “Task 1” is expanded. If it returns 0, the said group is collapsed.

What about Finding the Collapsed Subgroups?

For example, let’s consider subgroup ‘c’ of “Task 1” which collapses and expands the rows 7 to 9.

Here the first cell to be tested for collapsed/expanded group will be cell B7.

The below formula in cell E6 can test whether the subgroup “c” is collapsed or expanded.

=if(subtotal(103,B7)=1,"Expanded","Collapsed")

Here are the formulas to total collapsed subgroups in Google Sheets. I will come to how to get the sum of collapsed groups later.

Return Total When Subgroup Is Collapsed in Google Sheets

In my example, there are two main groups. They are “Task 1” and “Task 2”. The “Task 1” has two subgroups and the “Task 2” doesn’t have any.

The “Task 1” subgroups are “c” and “f”.

The formula in cell B6 to total when subgroup “c” is collapsed.

=if(subtotal(103,B7)=1,,sum(B7:B9))

Note: This is a temporary formula and you can see a different formula in my experiment sheet shared. We will replace this formula with that later. There is a reason for using a temporary formula right now.

To know the logic behind this formula just scroll up and check the formula that you see first.

The “Expanded” text in that formula is replaced by blank (none) and “Collapsed” text is replaced by sum(B7:B9).

That means when the subgroup “c” is collapsed, the formula executes sum(B7:B9) else returns blank.

The (temporary) formula in cell B12 to total when subgroup “f” is collapsed.

=if(subtotal(103,B13)=1,,sum(B13:B15))

This way you can get the sum of collapsed subgroups in Google Sheets.

Return Total When Group Is Collapsed in Google Sheets

When you collapse a group (parent category), the subgroup (child category) if any, will also get collapsed.

In the example, we have two subgroups (child categories) under the group “Task 1”.

We have formulas in cell B6 and B12 to get the total when corresponding subgroups are collapsed.

When the parent group “Task 1” collapses, the formulas in cell B6 and B12 will return the sum of collapsed subgroups even though that rows will also be hidden.

This makes one hurdle using the above two collapsed subgroup formulas in collapsed groups.

As per the above formulas that sum rows in column B when subgroups are collapsed, the formula here would be;

=if(subtotal(103,B4)=1,,sum(B4:B15))

It would return a wrong total as it will include the totals in cell B6 and B12.

The solution is replacing the SUM functions with SUBTOTAL in all the formulas above (two subgroup formulas and the just above group formula).

The two temporary formulas in cell B6 and B12 to be replaced with;

=if(subtotal(103,B7)=1,,subtotal(109,B7:B9))

and

=if(subtotal(103,B13)=1,,subtotal(9,B13:B15))

respectively.

The formula in cell B3 will be;

=if(subtotal(103,B4)=1,,subtotal(9,B4:B15))

Subtotal Formula Explanation

The function code to sum (SUM function equivalent) in SUBTOTAL is 9 and 109.

We must use 9, not 109 as 9 will ignore grouped or manually hidden rows. It will only consider the DATA menu filtered out rows as hidden.

Further, it will exclude any SUBTOTAL formula outputs within its range. This comes relevant in the last formula above where the range B4:B15 contains two SUBTOTAL formulas.

What about the formula to total when “Task 2” is collapsed in B16?

I hope you can write that formula yourself. Here it is.

=if(subtotal(103,B17)=1,,subtotal(9,B17:B19))

That’s all about how to total only when groups/subgroups are collapsed in Google Sheets.

Related Resources

Here is a different approach using helper columns.

• Grouping and Subtotal in Google Sheets and Excel.

In the below example I have used grouping (doesn’t include sum) to format the Gantt chart data.

• Create a Gantt Chart Using Sparkline in Google Sheets.

As you can see, I have applied indentation on the description column of the sample data used in this tutorial. Actually Google Sheets doesn’t have such a feature. But there is a quick workaround.

• Increase Decrease Indent in Google Sheets [Macro].