Inserting Subtotal Rows in a Query Table in Google Sheets

Here is a simple hack to help you to insert subtotal rows in a Query Table in Google Sheets. You can use this method if you don’t want to use the built-in Pivot Table.


In Pivot Table, you can get totals (subtotals) and a grand total row. The total rows are for section-wise (group-wise) totals whereas the grand total row is for the sum of section-wise totals.


Before start learning how to insert subtotal rows in a query table in Google Sheets, see how it compares with a pivot table.


One of the main advantages of the Query table that contains subtotals is it can retain the positions of the columns as per the original data. It’s flexible. So you can move the columns around. In the pivot table, it won’t be the case.


For example, let’s compare the below two outputs – the Query table in F1:I8 and the Pivot Table in F10:I18. As you can see, the Query table retains the column positions in comparison to the source data.


Formula to Insert Subtotal Rows in a Query Table in Google Sheets
image # 1

Note:- It’s quite easy to add the Grand Total row to the Query Pivot table. I have already explained the same earlier (please see the ‘Resource’ section at the end of this tutorial). So I am skipping it.


To insert subtotal rows in a Query table, we will use three query formulas in nested form.


Sample Data:


Please check the range A1:D6 in the screenshot above.


How to Insert Subtotal Rows in a Query Table in Google Sheets


Since there are three Query formulas involved, the number of steps will also be three. Among the three, the second Query formula is the most important one as it generates subtotal rows to insert.


The first and second Query will generate two tables. The purpose of the third Query is to combine them properly.


Related: How to Combine Two Query Results in Google Sheets.


None of the below formulas are complicated. If you face any issue, you may think about spending some of your leisure time learning Query.


Must Check: Google Sheets Function Guide [Quickly Learn All Popular Functions].


Step 1: Select Columns and Eliminate Blanks (Table 1)


This step has nothing to do with Subtotal Rows. But it is one of the core parts of the formula to insert subtotal rows in a Query Table.


Insert this formula in cell F1. It will return the table in the range A1:D as it is.


=query(A1:D,"Select A,B,C,D where A is not null",1)

There are four columns in my table. They are Item, Qty 1, Date, and Qty 2.


I am selecting all the columns. If you want, you can choose only the columns that you want. You can also change the column positions.


If you want to skip or change column positions, do the same by modifying the formula part A,B,C,D in the Select clause of Query.


For example, to select Item, Qty1, and Qty 2, replace A,B,C,D with A,B,D.


Depending on the same, there will be changes in the step 2 formula below. So, for the time being, stick with my above formula. Once learned, you may adjust the columns.


As I have already mentioned, the purpose of the above formula is to return required columns and eliminate the blank rows, if any.


It’s time to pay attention to the step 2 formula below, which is the key to insert subtotal rows in a Query table in Google Sheets.


Step 2: Key Formula to Generate Subtotal Rows (Table 2)


The below formula goes in cell F8. We will combine F1 and F8 formulas in the final step.


=QUERY(ArrayFormula(if(len(A2:A),A2:A&" Total",B2:D,)),"Select Col1,sum(Col2),' ',Sum(Col4) where Col1 is not null group by Col1 label sum(Col2)'',Sum(Col4)'',' '''")

As you can see it returns the subtotal rows to insert in the Query table.


Inserting Subtotal Rows in a Query Table - The Key Step
image # 2

In the third step, we will combine the step 1 and step 2 result vertically and then sort column 1 in ascending order. That way, we can insert subtotal rows in a Query table in Google Sheets.


Before going to that final step, let’s spend some time understanding the step 2 formula.


I know I should explain the formula here in detail. Let me start with the Query syntax and then the formula.


QUERY(data, query, [headers])

There are three arguments. Let’s see how each one of them contributes to the formula.


Data Part:


In the above formula, the data is ArrayFormula(if(len(A2:A),A2:A&" Total",B2:D,)).


It simply returns the source data in A1:D as it is. The only change is it adds the text “Total” at the end of the values (items) in A2:A.


That means all the “apple” will become “apple total” and all the “orange” will become “orange total”. Please refer to A11:I15.


The Data Part of the Formula
image # 3

Let me elaborate on the formula further as it plays a vital role in inserting subtotal rows in the Query Table in Google Sheets. You feel free to jump to the next subtitle below.


The Query ‘data’ is an IF logical test.


Syntax: IF(logical_expression, value_if_true, value_if_false)


The logical_expression is the formula len(A2:A). It identifies the non-blank cells by calculating the length of characters.


If the len(A2:A) returns numbers, that means, such cells have values.


This formula tests whether there are values in the range A2:A.


The Value if True Part of the Formula
image # 4

The value_if_true (TRUE) in the IF logical test is A2:A&" Total",B2:D. We have not used the value_if_false (FALSE).


Query Part:


The query part is as follows to summarize the ‘data’ above.


"Select Col1,sum(Col2),' ',Sum(Col4) where Col1 is not null group by Col1 label sum(Col2)'',Sum(Col4)'',' '''

It returns column 1 (Item), sums column 2 (Qty 1), returns a blank column instead of the “Date” column, and sums column 4 (Qty 2). Further, it groups column 1.


Please see image # 1. You can see that, in the subtotal rows, which we have inserted, the cells in the “Date” column are blank. There are values in other columns in the subtotal row.


We have used ' ' to insert a blank column. You can find more details on that here – How to Insert Blank Columns In Google Sheets Query.


We didn’t use the header part. Our “data” in the above case has no header row.


We can either use 0 or left the header blank to let the Query detect it automatically. I have opted for the latter.


Step 3: Insert Subtotal Rows in the Query Table by Combining Table 1 and 2


In the third Query formula, the “data” is the vertically combined step 1 and 2 tables. That means the step 2 table is placed below the step 1 table.


=query(step_1_formula;step_2_formula,"Order by Col1",1)

The step 3 formula is only for combining the two tables and then sorting column 1 (item) in ascending order. It will insert the subtotal rows below each section (each item).


Conclusion


I have a few similar tutorials under the Resources section below. Please check them too.


That’s all about how to insert subtotal rows in a Query Table in Google Sheets. Thanks for the stay. Enjoy!


Sample_Sheet_18421


Resources:

Disqus Comments

Hot Posts