How to add a total row to a Query Function table in Google Sheets

This article looks at how to add a total row to tables generated using the Query function in Google Sheets. It’s an interesting use case for array formulas, using the {...} notation, rather than the ArrayFormula notation.

So what the heck does this all mean?

It means we’re going to see how to add a total row like this:

How to add a total row to a Google Sheets QUERY table
Table on the left without a total row; Table on the right showing a total row added

using an array formula of this form:

= { QUERY ; { "TOTAL" , SUM(range) } }

Now of course, at this stage you should be asking:

“But Ben, why not just write the word TOTAL under the first column, and =SUM(range) in the second column and be done with it?”

Well, it’s a good question so let’s answer it!

The reason for using this method is because the total line is added dynamically, so it will be appended directly at the end of the table, and won’t break if the table expands or contracts, if more data is added.

It’ll always move up or down, so it sits there as the final row.

Simple example of how to add a total row

Before we get to the QUERY function example, let’s try a super simple one to understand the mechanics of the array formulas we’re going to be building.

Imagine I have this dataset:

Simple data example

Like I said, keeping things simple.

Step 1: Combine tables using array

Let’s manually create a total row next to our original table, in cells D1 and E1, like so:

Simple data example 2

Then we can use this formula, in cell G1, to combine these two tables into a single one:

={A1:B2;D1:E1}

which, in our Google Sheet, looks like this:

Simple data example output

The syntax is a pair of curly braces and a semi-colon to say the two tables should be combined vertically.

To that end, each table must have exactly the same number of columns.

Step 2: Create a total line using an array

Now, let’s use an array formula to generate that total line. Instead of typing “Total” into one cell, and a number into the adjacent cell, simply create the total line with a single formula:

={"TOTAL",3}

The syntax is a pair of curly braces and a comma to say the two elements should be combined horizontally.

To that end, each original element must have exactly the same number of rows.

Step 3: Use a SUM formula in total array table

Change the above formula to include a SUM function as follows:

={"TOTAL",sum(B1:B2)}

Step 4: how to add a total row to the table with the array

Using the formula from Step 1, replace the second element in the array (the D1:E1) with the formula from Step 3, so your output formula is now:

={A1:B2;{"TOTAL",sum(B1:B2)}}

This gives you your answer:

Nested array formulas to the rescue
Nested array formulas to the rescue!

Step 5: Use indented notation in formula bar

This is purely a presentational step, to make the formula a little more readable inside of the formula bar. Simply add some line breaks (Ctrl + Enter) and indentations:

={
    A1:B2
  ;
    {
      "TOTAL",
      sum(B1:B2)
    }
  }

Ready?

Ok good, let’s get back to the main example:

Total row example with QUERY function

For this example we’ll use some New York City data, specifically data about how many open positions there are for different agencies within the city.

Total row raw data

What we’d like to do is summarize the number of positions for each agency, i.e. combine all the agency lines into single lines with a total count for that agency.

We’re “grouping” our data into the categories listed in column A, and adding up all of the values in column B that fall into each group.

To achieve that, we use the QUERY function with a group by clause, to aggregate the number of positions data for each agency:

=QUERY($A$11:$B$61,"select A, sum(B) group by A order by sum(B) desc label sum(B) 'Total Positions'",1)

which gives an output like this:

Query table with Group By

Ok, so far so good. Nothing new to see here.

(Learn about or refresh your memory on the QUERY function here.)

How do we go about adding that total row then?

How to add a total row:

Essentially what we’re doing is exactly the same as the simple example above, creating two separate tables (one is the summary table, like the one above, the other is the total row) and then we use an array formula to combine them into a single table.

Here’s a pseudo formula to illustrate what we’re doing:

= { QUERY ; TOTAL } <-- notice use of semi-colon ;

and then the Total is actually it’s own array formula as we saw:

{ "TOTAL" , SUM(range) } <-- notice use of comma ,

so that the final formula, a nested array formula, takes this form:

= { QUERY ; { "TOTAL" , SUM(range) } }

So let’s go ahead and nest the QUERY function inside of the array formula, with an array SUM formula for the total:

={QUERY($A$11:$B$61,"select A, sum(B) group by A order by sum(B) desc label sum(B) 'Total Positions'",1);{"TOTAL",SUM($B$11:$B$61)}}

(Optional) Let’s add some line breaks and indentations to make things a little clearer (you can do this in your formula bar too! Use Ctrl + Enter to insert new lines into your formulas and make them more readable):

={
  QUERY(
    $A$11:$B$61,
    "select A, sum(B) group by A order by sum(B) desc label sum(B) 'Total Positions'",
    1
  )
;
  {
    "TOTAL",
    SUM($B$11:$B$61)
  }
}

The following image shows how the different parts of the formula output data in the table, with the array syntax added in red:

How to add a total row to a Google Sheets QUERY table

Final step to make it dynamic

There’s one last tweak we need to make if we want it to be dynamic, so that the total row moves up or down as the table expands when we add or remove data, and exhibit behavior like this:

Add data demo
Raw data is in the left pane. Of the two tables, the static table (middle of image) does not change, but the dynamic table (right of image) updates so the total row moves down to accommodate the new data.

There are two things we need to do: 1) adjust the range to include whole of columns A & B, 2) adjust the QUERY function to remove the blank row that gets added after step 1).

Adjust the range by removing the $61 references so we include the whole of columns A and B. Then tweak the QUERY function by adding a WHERE clause to exclude all the blank rows from column A.

The final function is:

={
  QUERY(
    $A$11:$B,
    "select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) 'Total Positions'",
    1
  )
;
  {
    "TOTAL",
    SUM($B$11:$B)
  }
}

Can I see an example worksheet?

Yes, here you go.

Feel free to make your own copy.

7 thoughts on “How to add a total row to a Query Function table in Google Sheets”

  1. Thank You! This is highly useful. Would one follow the same syntax if the query had two columns to sum? Would you be able to post an example of multiple columns to sum?

    1. Yes, you can use the same syntax for more columns, you just need to be sure that the number of columns in your QUERY table matches the number of columns in your TOTAL row table.

  2. Ben, many thanks for all those helpful tutorials!

    One question, though…
    Why don’t you put a “where A ” ” clause instead of the “limit” to not take the blank rows?
    Wouldn’t that be better, allowing more ordering options in the results?

    1. Good call Max D.!

      Both solutions work, but I like using the WHERE clause because it’s less complex and relies on one less nested formula. Thanks. Have updated the article to reflect this.

Leave a Reply

Your email address will not be published. Required fields are marked *