Graphed LogoGraphed
Login

How to Get Sum of Only Filtered Data in Excel

Cody Schneider•

When you filter data in Excel, you expect your formulas to update automatically to reflect only the visible information. However, if you've ever used the standard =SUM() formula on a filtered list, you’ve likely been frustrated to see it stubbornly include all the hidden rows in its calculation. Fortunately, Excel has specific functions designed to work intelligently with filtered data.

This article will show you exactly how to sum only the visible cells in your filtered lists. We’ll cover two powerful functions, SUBTOTAL and AGGREGATE, that give you complete control over your calculations.

Why SUM() Fails 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 straightforward: it adds up all the numbers in a range you specify. It doesn't care whether cells are visible, hidden, or filtered out. Its job is to sum everything in the range, period.

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

Sales Data Example:

If you filter the "Region" column to show only "East" and have a =SUM(D2:D13) formula at the bottom, your total will remain $7,025. Excel is still summing the "West" and "North" region sales - you just can't see them.

This is where our two purpose-built functions come in.

The Standard Solution: Using the SUBTOTAL Function

The SUBTOTAL function is the most common and direct way to perform calculations on filtered data. It's designed specifically to recognize filters and only include visible cells in its calculations. You can use it to average, count, find the max/min, and, of course, sum a filtered list.

The syntax for the SUBTOTAL function is:

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

  • function_num: This is a special number that tells Excel which calculation to perform (SUM, AVERAGE, COUNT, etc.).

  • ref1: The range of cells you want to calculate (e.g., D2:D13).

Understanding the function_num

This is the most critical part of the formula. Excel offers two sets of function numbers. The first set ranges from 1-11, and the second from 101-111.

  • Numbers 1-11: These perform calculations on filtered data but include any rows that were manually hidden (by right-clicking and selecting "Hide").

  • Numbers 101-111: These perform calculations on filtered data and also ignore any manually hidden rows. They only consider cells that are genuinely visible on the screen.

For summing filtered data, you have two choices for your function_num:

  • 9 = SUM (includes manually hidden rows)

  • 109 = SUM (ignores manually hidden rows)

As a best practice, it's almost always safer to use 109. This ensures your calculation only ever includes what you can literally see, guarding against accidentally forgotten hidden rows.

Step-by-Step Guide to Using SUBTOTAL

Let's use our previous sales data example to get the correct sum for the "East" region.

  1. Position your total cell. Click into the cell where you want your dynamic total to appear, such as D15.

  2. Enter the SUBTOTAL formula. Type the following formula and press Enter:

=SUBTOTAL(109, D2:D13)

  • 109 tells Excel to SUM only the visible cells.

  • D2:D13 is our range of sales figures.

Before any filters are applied, the result will be the grand total, just like the regular SUM function.

  1. Apply Your Filter. Now, filter the "Region" column to show only "East."

  2. Watch the Magic Happen. Observe your total in cell D15. It automatically updates to display the correct total for only the visible "East" region sales: $3,050.

Now, whenever you change or clear the filter, your SUBTOTAL formula will instantly adjust its calculation.

A More Powerful Alternative: The AGGREGATE Function

Introduced in Excel 2010, the AGGREGATE function is like SUBTOTAL on steroids. It can do everything SUBTOTAL can and more, with its most significant advantage being the ability to ignore errors within a range.

If you've ever had your entire sum formula break because of a single #N/A, #REF!, or #DIV/0! error someone left in a cell, you’ll appreciate AGGREGATE.

The syntax for AGGREGATE looks similar but has one extra, powerful argument:

AGGREGATE(function_num, options, array)

  • function_num: A number representing the calculation. For AGGREGATE, SUM is also number 9.

  • options: A number that tells Excel what types of values to ignore in the calculation range.

  • array: The range of cells to calculate (e.g., D2:D13).

Choosing the Right options

The options argument is where AGGREGATE truly shines. You get precise control over what to ignore. For our purpose of summing filtered data, the most useful options are:

  • Option 5: Ignore Hidden Rows. (This is the direct equivalent of SUBTOTAL(109, ...))

  • Option 7: Ignore Hidden Rows and Error Values. (This is incredibly useful for messy data.)

Step-by-Step Guide to Using AGGREGATE

First, let's replicate what we did with SUBTOTAL.

  1. Click into your total cell (D15).

  2. Enter the AGGREGATE formula:

=AGGREGATE(9, 5, D2:D13)

  • 9 specifies the SUM function.

  • 5 tells it to ignore hidden rows (from filtering).

  • D2:D13 is the data range.

  1. Filter your data. The result will be identical to the SUBTOTAL function, dynamically updating as you filter.

The Key Advantage: Handling Errors Gracefully

Now let's see why AGGREGATE is often the better choice. Suppose your data export contains an error in one of the cells.

If you use SUBTOTAL on this range, your result will be #N/A, even if the cell with the error is filtered out. The entire calculation breaks.

With AGGREGATE, we can just switch our option number. Use this formula instead:

=AGGREGATE(9, 7, D2:D13)

Note we've changed the option from 5 to 7 (Ignore Hidden Rows and Error Values). Now, Excel happily calculates the sum of all the valid numbers in the range, skipping over the error completely.

This makes your spreadsheets much more robust and less prone to breaking when dealing with imperfect real-world data.

Quick Tip: Using Excel Tables for Automated Totals

If you aren't keen on memorizing function numbers, you can let Excel do the work for you by formatting your data as an official Excel Table.

  1. Create a Table: Click anywhere inside your data range and press Ctrl + T (or go to Insert > Table). Make sure the "My table has headers" box is checked.

  2. Enable the Total Row: Once your data is a table, a "Table Design" tab will appear on the ribbon. Click it, then check the box for "Total Row".

  3. Select Your Calculation: A new row will appear at the bottom of your table. Click into the cell under your "Sales" column, and a dropdown arrow will appear. Simply select "Sum" from the list.

Behind the scenes, Excel automatically inserts a SUBTOTAL formula for you! This is the fastest, no-code way to get dynamic totals that respond to filters.

Final Thoughts

While the standard SUM function falls short with filtered data, you now have a complete toolkit to get the job done right. Use SUBTOTAL for a quick and reliable solution, switch to the more powerful AGGREGATE when dealing with messy data containing errors, or format your range as an Excel Table to let the interface handle it for you.

Constantly manipulating spreadsheets to find simple answers can feel like a chore, especially when your data lives in multiple places. Instead of spending your time filtering rows and writing formulas just to calculate regional sales, we built a tool that lets you ask questions directly. With Graphed, you simply connect your data sources (like your e-commerce store, ad platforms, and CRMs) once. From there, you can ask in plain English, "What were my total sales in the East region last quarter?" and get an instant, real-time dashboard that updates automatically, saving you from the routine cycle of manual spreadsheet work for good.