How to Total Filtered Data in Excel

One of the most common tasks in Excel is summing a column of numbers, but when you apply a filter, you’ll quickly notice the standard =SUM() formula doesn’t give you the answer you need. Instead of summing only the visible cells, it totals the entire dataset, including the rows you’ve hidden. This article will show you exactly how to total only your filtered, visible data using Excel’s powerful SUBTOTAL and AGGREGATE functions.

Why Does the =SUM() Formula Fail with Filtered Data?

Before we jump into the solution, it's important to understand the problem. The SUM function is designed to be simple and direct, it adds up every number in the range you specify, regardless of whether the cell is visible or hidden by a filter.

Imagine you have a sales report like the one below. You want to see the total sales for just the "East" region.

If you filter the "Region" column to show only "East" and have a =SUM(C2:C10) formula at the bottom, your total will remain stubbornly unchanged. It will continue adding up the sales from all regions, including the "West" and "North" rows that are now hidden. This is why you need a function that is smart enough to recognize and ignore filtered rows.

Solution 1: Use the SUBTOTAL Function for Quick Totals

The SUBTOTAL function is the easiest and most common way to solve this problem. It’s built specifically to perform calculations on a range of cells while ignoring rows that have been filtered out.

Understanding the SUBTOTAL Syntax

The syntax for the function looks like this:

SUBTOTAL(function_num, ref1, [ref2],...)

function_num: This is a number that tells Excel which calculation to perform (SUM, AVERAGE, COUNT, etc.). This is the most important argument to get right.
ref1: This is the range of cells you want to calculate (e.g., C2:C10).

The key to SUBTOTAL is the function_num. You have two sets of numbers to choose from, and the difference is crucial:

Numbers 1-11: These numbers perform calculations that include rows that were manually hidden (by right-clicking and selecting "Hide") but exclude rows hidden by a filter.
Numbers 101-111: These numbers perform calculations that exclude all hidden rows, whether they were hidden manually or by a filter.

For summing filtered data, you will most often use 9 or 109.

9 = SUM (ignores filtered rows, includes manually hidden rows)
109 = SUM (ignores all hidden rows)

For most day-to-day filtering, using the number 9 for SUM is perfect.

Step-by-Step Guide to Using SUBTOTAL

Let's use our previous sales data example to walk through the process.

1. Set Up Your Data and Filter

Make sure your data is in a structured list with headers. Select your header row, go to the Data tab, and click the Filter button. You should see dropdown arrows appear in your header cells.

2. Place Your SUBTOTAL Formula

It’s best practice to place your total formula either above your data headers or in a row well below your data. Putting it directly under your column can cause it to be hidden when the filter is applied. Let’s place it in cell C11 for this example.

Click on cell C11 and type the following formula:

=SUBTOTAL(9, C2:C10)

Initially, this formula will show the total for all sales, which is correct since no filter has been applied yet.

3. Apply a Filter and Watch it Work

Now, click the filter arrow in the "Region" header and uncheck everything except "East."

The moment you apply the filter, the result of your SUBTOTAL formula instantly updates. It now shows the sum of only the visible "East" region sales, giving you the correct, context-aware total. If you change the filter to "West" or select multiple regions, the total will continue to update dynamically.

Solution 2: Use the AGGREGATE Function for More Power

While SUBTOTAL is perfect for most situations, the AGGREGATE function offers even more flexibility and control. Think of it as SUBTOTAL on steroids. Its biggest advantages are the ability to ignore hidden rows but also ignore error values within your data range.

Have you ever had a #DIV/0! or #N/A error in your column that breaks your entire =SUM() or =SUBTOTAL() formula? AGGREGATE solves this.

Understanding the AGGREGATE Syntax

The syntax is slightly more complex, but it gives you more options:

AGGREGATE(function_num, options, array, [k])

function_num: Like SUBTOTAL, this is a number representing the calculation you want. For SUM, this is still 9.
options: This is where AGGREGATE shines. It’s a number that tells Excel what types of values to ignore in the calculation.
array: This is your range of data.

For the options argument, you have several choices, but here are the most relevant ones for summing filtered data:

3: Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.
5: Ignore hidden rows.
7: Ignore hidden rows and error values.

Option 7 is often the most practical choice, as it handles both filtered rows and potential errors in your dataset.

Step-by-Step Guide to Using AGGREGATE

Let's modify our sales report. Suppose a data entry error led to a #DIV/0! value in our sales column.

A formula like =SUBTOTAL(9, C2:C10) would now return a #DIV/0! error itself because it's trying to include that errored cell in its calculation.

Here’s how to use AGGREGATE to fix it:

1. Select Your Cell for the Total

Click on cell C11 where you want your total to appear.

2. Enter the AGGREGATE Formula

Type the following formula:

=AGGREGATE(9, 7, C2:C10)

Let's break that down:

9 tells AGGREGATE to perform a SUM.
7 tells it to ignore both hidden rows and error values.
C2:C10 is the range of sales data we want to sum.

3. Check the Result

The formula immediately returns the correct sum, gracefully skipping over both the error cell and any rows hidden by a filter. If you apply a filter for the "East" region, the formula will correctly total the visible rows while continuing to ignore the error, giving you a robust and accurate result no matter how you slice your data.

Pro Tip: Use Excel Tables

To make your life even easier, convert your data range into an official Excel Table before you even start.

Click anywhere inside your data.
Press Ctrl + T (or on a Mac, Cmd + T).
Make sure the "My table has headers" box is checked and click OK.

Now, when you select the Table Design tab that appears, you can check the "Total Row" box. Excel will automatically add a total row at the bottom of your table that uses the SUBTOTAL function by default! You can even click on the total cell and choose a different calculation like Average or Count from a dropdown menu, all without ever typing a formula.

Final Thoughts

To sum up, when you need to total filtered data in Excel, the standard =SUM() function won't work. Your go-to solution should be the =SUBTOTAL(9, range) formula, which is designed to calculate only visible cells. If your data contains error values that break your calculations, level up to the more powerful =AGGREGATE(9, 7, range) function to ignore hidden rows and errors simultaneously.

While mastering Excel functions like these is invaluable, sometimes the C-suite wants an instant, automatically updating Shopify and Stripe revenue breakdown right before the board meeting. When that manual data wrangling won’t cut it, we made working with data effortless using natural language. At Graphed , you can securely connect all your key data platforms (like Google Analytics, Shopify, QuickBooks Online), your sales and marketing technology, and spreadsheets to your centralized data dashboard so you can find the answers to all your important questions using simple, everyday language as easily as asking your phone the weather.