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:

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(`

in the second column and be done with it?”range)

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:

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:

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:

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:

### 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.

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:

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:

## 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:

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?

Feel free to make your own copy.

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?

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

matchesthe number of columns in your TOTAL row table.Very useful…

Thank You for making my life easier ðŸ’–

Fantastic post, thank you very match

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?

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.

I have more than just two rows and for some reason, it is saying:

1) That I have a circular dependency

2) Once I allow for one circular reference, it totals to 0

What’s your formula? Also, if you’re based in continental Europe, the “;” and “,” are the other way around…

`={QUERY(Master,"Select B, sum(D), sum(E), sum(F), sum(G) Where A>= date '"&Text(B1,"yyyy-mm-dd")&"' AND A<= date '"&Text(D1,"yyyy-mm-dd")&"' AND("&Trim("upper(B) contains '"&Upper(JOIN("' OR upper(B) contains '",FILTER(G2:G,NOT(ISBLANK(G2:G)))))&"'")&") group by B label sum(D) 'Cost', Sum(E) 'Impr', sum(F) 'Inter.', sum(G) 'Conv.' " , 1); "Total",Sum(B3:B),Sum(C3:C),Sum(D3:D),Sum(E3:E)}`

Firduash – you need to add curly brackets {} around your total line, like this:

`{"Total",Sum(B3:B),Sum(C3:C),Sum(D3:D),Sum(E3:E)}`

so your full formula becomes:

`={QUERY(Master,"Select B, sum(D), sum(E), sum(F), sum(G) Where A>= date '"&Text(B1,"yyyy-mm-dd")&"' AND A< = date '"&Text(D1,"yyyy-mm-dd")&"' AND("&Trim("upper(B) contains '"&Upper(JOIN("' OR upper(B) contains '",FILTER(G2:G,NOT(ISBLANK(G2:G)))))&"'")&") group by B label sum(D) 'Cost', Sum(E) 'Impr', sum(F) 'Inter.', sum(G) 'Conv.' " , 1); {"Total",Sum(B3:B),Sum(C3:C),Sum(D3:D),Sum(E3:E)}}`

Hope that helps!

Ben

Hi Ben,

Unfortunately, it did not help. I still receive a circular dependency error. When I allow one iteration, it does not add, the totals row remains all zero. I tried adjusting the formatting of the raw data but that doesn’t make a difference. I used only two columns table to stay in line with your example but adding one column ends in a circular dependency or a 0 value.

Can you share your sheet? If I have time I’ll try to take a look. Cheers, Ben

Also, you need to sum the original data, not the data in the new query table, otherwise you’ll get circular dependency errors.

Hi Ben,

I had the same issue with circular dependency errors and found a hack.

={query(A:C,”Select A, Sum(B),Sum(C) where A is not null group by A order by sum(B) desc label sum(B) ‘Total Data1’, sum(C) ‘Total Data2′”);{“Total”, index(query(A:C,”select sum(B)”),2,1),index(query(A:C,”select sum(C)”),2,1)}}

I Basically query the Totals only and then wrap an INDEX around them.

Here is a link to the sample sheet https://goo.gl/rWK9NT

Hey Shane,

Thanks for sharing your solution, nice use of index ðŸ™‚

However, you shouldn’t get the circular errors if you’re summing the original data, with a straightforward sum formula.

Cheers,

Ben