If you’re tired of manually adding totals to your filtered data in Google Sheets, then you’re in luck. In this article, I’ll show you how to automatically add a total to a filtered data using Google Sheets functions. No more hassle, just dynamic and accurate totals!
Google Sheets Query Function and Filtering
When we use the QUERY function to filter data in Google Sheets, we can easily add a total to the end of each row. This means that the total adjusts automatically when you change your data and even stays in place as you filter your data.
In the image above, we have our sample data. I’ve filtered Column D for the value “Safety Helmet” and then totaled Column H based on the filter. As you can see, a new row with the total has been added at the end of the filtered table.
Steps to Automatically Add Total to a Filtered Data
To automatically add a total to your filtered data, we’ll be using the highly useful Google Sheets QUERY function. There are two steps involved: filtering the data and adding a total row to the end.
Formula #1
=query(A1:H12,"Select * where D='Safety Helmet'")The above QUERY formula filters Column D for the value “Safety Helmet” and returns the filtered data.
Now that we have the filtered data, how do we add the total row at the end? There are two options: adding a new row automatically or manually adding a total using the SUBTOTAL function. We’ll go with the first method, which is more ideal.
Formula #2
=query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")The above QUERY formula calculates the sum of Column H when Column D value matches “Safety Helmet”. The label part of the formula removes the label “Sum(H)” so that we have just the result value.
To combine these two formulas, we’ll use curly brackets. Here’s how it’s done:
={query(A1:H12,"Select * where D='Safety Helmet'"); query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")}By placing an open curly bracket at the beginning of the first formula and a closing curly bracket at the end of the second formula, we can nest the two formulas and get the desired result. Note that we use a semicolon to separate the formulas, not a comma.
How to Nest Query Formulas in Google Sheets
Nesting query formulas in Google Sheets is easy. Simply place an open curly bracket at the beginning of the first formula, use a semicolon to separate the formulas, and place a closing curly bracket at the end of the last formula.
Here’s an example of how it’s done:
={query(A1:H12,"Select * where D='Safety Helmet'"); query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")}Remember, using curly brackets and nesting formulas allows you to combine and manipulate data in powerful ways.
Conclusion
In my opinion, learning how to use the QUERY function and nest formulas in Google Sheets is a valuable asset. It can save you a lot of time and effort in the long run. Now that you know how to automatically add a total to a filtered data in Google Sheets, go ahead and give it a try. Enjoy the convenience and accuracy of dynamic totals!
For more tips and tricks on Google Sheets, visit Crawlan.com.
