How to sum filtered rows and columns in Google Sheets

Google Sheets has proven to be a powerful tool for executing complex calculations and analysis. And in today’s guide, we will go over everything you need to know about how to sum filtered rows and columns in Google Sheets.

Whether this is your first time using Google Sheets to sum filtered rows and columns or something you have tried before, this guide will provide you with exciting perspectives that will make your job easy.

Let’s cut to the chase and bring you all the exciting details, shall we?

Summing filtered rows and columns in Google Sheets: Understanding the basics.

If this is your first time attempting to sum filtered rows and columns in Google Sheets, it might sound a bit challenging. But not to worry, we are here to guide you every step of the way. Like every of our other Google Sheets tutorials, today’s guide is all about taking a complex issue and simplifying it.

While there are several ways to sum filtered rows and columns in Google Sheets, today’s guide highlights the easiest way. And for us, the easiest way to do it is by using the following syntax:

SUBTOTAL(109, A1:10)

We are sure you might wonder where we got the value “109” from. Actually, it is a shortcut for taking the sum of a filtered range of rows.

Read on as we show you a practical example of how to use this function to sum filtered rows and columns in Google Sheets.

Practical example: how to sum filtered rows in Google Sheets

Having given you some background on how to sum filtered rows and columns in Google Sheets, it’s time to give you a practical example of how it is done. For this Google Sheets tutorial, we will work with the following data set showcasing football teams and the points earned for the season.

Before we can sum filtered rows and columns for the above data set, we first need to add a filter, which is super easy. First, we need to highlight cells A1:B10. You can do this manually or use the keyboard shortcut, Control A if you use a Windows computer, or Command + A if you use a Mac. After highlighting the cells, you need to navigate to the Data tab and click Create a filter. Check out the image below that gives a graphical illustration of the steps outlined above.

After selecting the “Create a filter option,” you should notice a filter icon next to the Football teams and Points columns. Next, we want to click on the Filter Icon close to the Points column and uncheck the box next to the following values: 68, 71, and 75.

Here is a video illustration showing you exactly how to execute the steps outlined above.

Notice that after clicking okay, Google automatically filtered our sample data to remove the values we unchecked.

Note: If we decide to use the SUM() function to sum the Point column of the filtered rows, what we get is the total sum of all the original values, which isn’t what we are interested in.

Let’s apply the SUM() function so you see exactly what we mean here.

Select the cell where you’d want to show the SUM() function. As per our example, we will use Cell B11 to display our result.

After choosing the cell where you want to highlight the result, head to the formula bar of your Google Sheets and type in the following formula:

=SUM(B2:B8)

Finally, hit the enter key on your keyboard, and you should see the result showcased in Cells B11.

The image below perfectly illustrates the steps outlined above.

From the result generated after applying the SUM() function formula, you’ll notice that what we get here is the total points for all teams included in our sample data, including the filtered rows, which isn’t what we want here. So you can clearly see that applying the SUM() function won’t give us exactly what we are looking for.

So instead of using the SUM() function to sum filtered rows and columns in Google Sheets, we can use the SUBTOTAL() function, which provides a more accurate result.

Here is how to use the SUBTOTAL() function to sum filtered rows and columns in Google Sheets.

First, select the cell where you want to showcase the sum of filtered rows and columns in Google Sheets. For this guide, we will use cell B11.
After choosing the cell where we would like to showcase the summed result, we need to type the following formula in the formula bar:

=SUBTOTAL (109, B2:B8)

After typing the formula, we can then hit the Enter key on our keyboard, allowing Google to automatically generate the result in our chosen cell.

If you followed the steps outlined above, your spreadsheet should look like this.

We used the SUBTOTAL() function to sum filtered rows and columns in Google Sheets because it only sums the values in the visible rows/column, which is what we are interested in, in the first place.

We can manually verify if the result generated by applying the SUBTOTAL() function is correct. All we need to do is sum the values in the visible rows.

So for our sample data, this is what it should look like: 98+78+96+88+83+82= 525.

Conclusion

It can be a bit challenging to sum filtered rows and columns in Google Sheets, especially if you are doing it for the first time. But thanks to today’s detailed guide on how to sum filtered rows and columns in Google Sheets, you should be able to sum filtered rows and columns in Google Sheets with ease.

When doing this for the first time, you want to ensure you don’t miss any steps. If you followed our guide, you’d notice that we first created a filter before actually summing the rows and columns of our sample data.

Also, we mentioned why using the SUM() function to sum filtered rows and columns in Google Sheets won’t work. We particularly mentioned that using the SUM() function will not give an accurate result.

Instead, we opted for the SUBTOTAL() function, which is the right formula for summing filtered rows and columns in Google Sheets.

If you are a bit confused about all the steps outlined in this post, feel free to review it once more for better clarity.

We hope today’s post has been quite helpful. For other exciting Google Sheets tutorials, feel free to check our blog, as we have plenty of Google Sheets tutorials we are sure you’ll love.

Frequently asked question

Can I sum only filtered rows in Google Sheets?

Absolutely, you can sum only filtered rows in Google Sheets. To make your job easy, you can use the SUBTOTAL() function to achieve that. Keep in mind that before you apply the SUBTOTAL() function, you’ll need to first create a filter.

How do I apply a formula to a filtered cell?

Applying a formula to a filtered cell is easy. All you need to do is head to the formula bar and type in the formula you’d like to use. It’s that easy.