You can automatically add a total to a filtered data in Google Sheets. It can bring much more dynamism to your filtered table. I am talking about filtering data using Google Sheets functions not by using the menu command.

In Google Sheets, you can use Functions like FILTER or QUERY to filter your Data. Both can return similar output. A normal filtered table may not contain a total at the end of the filtered table. Can we get an additional row at the end of the filtered table containing total like SUM, AVERAGE etc.? Yes we can do that by using QUERY function.

As a side note, when you have a data which is filtered using Google Sheets Data menu Filter Command, do not use SUM function to manually total it. Instead, use the SUBTOTAL function as detailed Here to avoid possible errors.

Google Sheets Query Function and Filtering

When we use QUERY function to filter data, we can automatically add the total to the end of row. It’s a dynamic total.

It not only adjust the total when you change your data but also keeps floating along with your filtered data.

The above is our sample data. Here I’m filtering Column D for value “Safety Helmet” and then total the column H based on the filter. Our filtered result will be as below. You can see that, at the end of the filtered row, a new row added for Total.

Then how to automatically add a total to a filtered data in Google Sheets as above. That we are going to learn here.

Steps to Automatically Add Total to a Filtered Data

As you already know, we are using the highly useful Google Sheets Query function here not the FILTER function.

There are two steps involved. One is normal filtering the data and the second one is adding a total row to the end.

Formula # 1

=query(A1:H12,"Select * where D='Safety Helmet'")

The above Google Sheets QUERY formula filters column D for value “Safety Helmet”. The result will be as below.

The above is the filtered data based on Column D value =”Safety Helmet”. This you can also do by using Google Sheets FILTER function.

Now we have done the filter part. If you want to learn Query function in details, switch to our tutorial Here.

How to get the total row at the end of this filtered table? There are two options.

1. Automatically add a new row at the end containing the total with another QUERY function.

2. Manually add a total using the SUBTOTAL Function.

We are following the first method which is ideal here. Why? Because if you add a total to the end of your filtered data manually, most of the time, you may get error value as below when you change your source data.

“Array result was not expanded because it would overwrite data in …”

Formula # 2

=query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")

What does this Query Formula do?

It SUMs Column H when column D value matches “Safety Helmet”. At the end part of the above QUERY formula, you can see “label Sum(H) ””. Without that, you would get the result as below.

The label part of the formula removes the label “sum Amount”. So we have the value 90 as result.

We have two formulas above. Now what we want to do is, add the second formula result to the end of the first formula result. That we can do by using Curly Brackets. See how I’m combining or nesting the above two Query formulas.

How to Nest Query Formulas in Google Sheets

We have two Query formulas above. How to combine them? First, see the combined formula below.

={query(A1:H12,"Select * where D='Safety Helmet'");
query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")}

The above formula may not work! I will come to that later. First, let me explain to you how to nest two Query formulas or how I did it in the above example?

At the beginning of the first formula place an open Curly Bracket. Then at the end of the first formula use a semicolon and add the second formula. After that at the end of the second formula, place a closing Curly Bracket. This’s what I did above though it may not work.

The semicolon is used in between two formulas to place the second formula result at the end of the first formula result. If you use a comma instead of the semicolon, the second formula result will be added to the right side of the first formula result, not to the bottom.

You can learn more about the use of Curly Braces or Curly Brackets from our tutorial below.

Use of Curly Brackets to Create Arrays in Google Sheets

As already mentioned, the above-combined formula may not work! Why? See the screenshot below.

As per the above combination, we are trying to place the total like this. Query function may not accept this as the total is of column H, that is the column with Column label “Amount”. So we need to move the total to that column. How to do that.

={"Total","","","","","","",90}

When you apply the above Google Sheets Array formula in any Cell, it will place the text string “Total” in the formula applied cell. Then it skips 6 columns to the right and on the 7th column, it will place the value 90.

We can replace the above value 90 with the second query formula above and then we can combine this new combination with the first Query formula. Now the final working combination will look like as below.

={query(A1:H12,"Select * where D='Safety Helmet'");{"Total","","","","","","",
query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")}}

Conclusion

In my opinion, you should spend your time to learn Query and Query Nesting. So it can be a valuable asset in your future carrier as you can save plenty of your man-hours using this. Hope you understood how to automatically add a total to a filtered data in Google Sheets. Enjoy!