Let’s learn how to insert group total rows in Google Sheets that also using a formula not using Google Apps Script.

I mean adding/inserting a row below each group/category that contains a total, for example, the total sales amount of groups, in a new range.

Please note, I’m not talking about or the topic is not about the Data (menu) > Group. That you can read here – How to Group Rows and Columns in Google Sheets.

When we have data (records) related to sales, purchases, expenses, or any category entered in sheets, we can sort that data to make groups.

For example, all the purchases from “company 1” will be in one group, “company 2” will be in another group, and so on.

If there is an amount column in such data (normally there would), can we add/insert group total rows in that data in Google Sheets?

To add group total rows what we do normally is we manually insert blank rows below each group and use the SUM or SUBTOTAL function to add the group total (in the resources section, you can find that topic).

The above manual method has two main disadvantages.

1. It would consume lots of time.
2. It would make our data unstructured. Data manipulation would be tough in such unstructured data.

What about using a formula in a new range to insert group total rows in Google Sheets?

Your data may contain several groups. No matter you have sorted it or not, I have a formula that will sort the data to make groups and add total rows below each group.

Steps to Insert Group Total Rows in Google Sheets

I’ll first show you my formula result. It may or may not be suitable for you. But you can follow the formula as I have one cool Sumif tip included that will be useful for you in the future.

There is a formula in cell I1, that inserts group total rows below each group and another formula in cell O1 that inserts the total of each group.

Both are array formulas. So you just insert them in the said cells and forget about that. The formulas will take care of your future entered data too (in B1:G).

In this tutorial, you can learn how to insert group total rows in Google Sheets in four easy to follow steps. Here we go!

Unique the Groups and Combine the String “Total” (Step # 1)

To get unique supplier names and add the string “Total” we can use the UNIQUE function (for sample data please refer to B1:G9 on the image). You will get to know the purpose of this on the course.

Assume I’ve used the below Unique formula in cell H2.

=unique(B2:B)

Of course, it would return the unique values from B2:B as below.

The Unique will leave a blank cell at the end since we have open range B2:B within Unique. To add the string “Total” with the above values we can modify the formula as below.

=ArrayFormula(
     unique(
        B2:B&" Total"
     )
)

How to remove the unwanted string “Total” in the last cell?

For that, we can use the function Filter with Unique.

Formula # 1:

=unique(
     filter(
        B2:B&" Total",B2:B<>""
     )
)

I assume step # 1 formula is simple for you to learn. Here is step # 2 to insert group total rows in Google Sheets.

Adding Blank Columns with the Single Column Unique Result (Step # 2)

In order to insert the above group total row within the source data, we must modify the above formula. Because our source data has a total of 6 columns. They are “Supplier’s Name”, “Inv No.”, “Qty”, “Unit”, “Unit Price”, and “Amt”.

We can’t combine the above Unique result with the source data because of the column numbers mismatch.

We can only combine similar size arrays. So we should add 5 blank columns to the unique formula result. Here is how.

Our data range is B2:G. We have created data to fill in column B (Unique formula). The columns left are C2:G.

To create blank cell columns corresponding to C2:G, use the below technique. I mean divide the values in the range C2:G by 0.

=filter(C2:G/0,B2:B<>"")

The above Filter will create a bunch of error cells in 5 columns. Insert this expression within the formula # 1 as below and see the result.

=unique({
     filter(
        B2:B&" Total",B2:B<>""
     ),
     filter(
        C2:G/0,B2:B<>""
     )
})

To remove errors and make the cells blank, replace C2:G/0 with iferror(C2:G/0).

Formula # 2:

=unique({
     filter(
        B2:B&" Total",B2:B<>""
     ),
     filter(
        iferror(C2:G/0),B2:B<>""
     )
})

Now we are set to insert group total rows (without total) within the source data. See step # 3 for that.

Insert Group Total Row (Step # 3)

To combine two data ranges/arrays we can use the Curly Brackets. Here is the generic formula that suitable for our scenario.

={B1:G;formula_2}

So the formula would be;

={B1:G;
unique({
     filter(
        B2:B&" Total",B2:B<>""
     ),
     filter(
        iferror(C2:G/0),B2:B<>""
     )
})
}

Using this formula in your sheet would cause freezing of sheet because B1:G contains several blank rows. Using Query we can filter out those unwanted rows.

So here is that Query formula to insert group total rows (without total column) in Google Sheets.

=query(
     {B1:G;
        unique({
           filter(
              B2:B&" Total",B2:B<>""
           ),
           filter(
              iferror(C2:G/0),B2:B<>""
           )
        })
     },
     "Select * where Col1 is not null order by Col1 asc"
)

Other than removing blank rows, I’ve included the “Order by” clause to sort the data. So that the inserted total rows will be correctly placed below each group.

Now to the final step of inserting group total rows in Google Sheets.

Add Total Column to Groups (Step # 4)

In cell O1, insert the below SUMIF which is a tricky formula.

={"Total";
ArrayFormula(
     sumif(I2:I&" Total",I2:I,N2:N)
)
}

The above formula places sum of groups against the row containing “Total” in column I.

All the other rows would get the value 0 which I’ve made hidden using conditional formatting. That tips (hiding) explained here in detail – Two Ways to Remove 0 Values in Google Sheets [How to].

Formula Explanation

“Total” – places the header.
I2:I&" Total" – Sumif range.
I2:I – Sumif criteria
N2:N – Sum range.

I’ve added the value “Total” to all the values in the range I2:I (in the Sumif range). At the same time, the criteria I2:I only contain the value “Total” with some of the values.

So the output will be only in the rows that contain “Total” physically in I2:I.

That’s all about how to insert group total rows in Google Sheets.

example_sheet_9620

Resources:

• How to Subtotal Up to the First Blank Cell in a Column in Google Sheets.
• Grouping and Subtotal in Google Sheets and Excel.
• Get Total Only When Group | Subgroup Is Collapsed in Google Sheets.
• Automatically Add Total to a Filtered Data in Google Sheets.