How to Sum Only Visible (Filtered) Cells in Excel

To sum visible cells only in Excel, replace SUM with SUBTOTAL. SUM adds every cell in the range, even rows a filter has hidden, so your total doesn't change when you filter. =SUBTOTAL(9,C2:C8) adds only the rows you can see. This guide explains the difference between SUBTOTAL 9 and 109, the AGGREGATE alternative, and how to sum visible rows with a condition.

Why SUM includes hidden rows#

SUM adds every number in its range, whatever is on screen. Filtering or hiding a row only changes what you see, not what's in the range.

The examples use this order list in A1:C8:

=SUM(C2:C8) returns 1,795. Now filter the Region column to show only North. Three rows are left on screen, adding up to 835, but SUM still says 1,795. That's the problem SUBTOTAL solves. The SUM function guide covers what SUM does include.

How to sum only filtered rows with SUBTOTAL#

Use function number 9, which tells SUBTOTAL to sum.

=SUBTOTAL(9,C2:C8)

With no filter, this returns 1,795, the same as SUM. Filter to North and it changes to 835 (250 + 175 + 410). Filter to South and it shows 490. The total always matches what's on screen.

To add a filter, click inside the table and press Ctrl+Shift+L on Windows or ⌘⇧F on Mac, then use the arrows in the header row. The lesson on filtering a table walks through it.

The quickest way to get this formula: filter first, then click the cell below the column and press AutoSum (Windows: Alt+=; Mac: ⌘⇧T). On a filtered list, Excel writes =SUBTOTAL(9,...) instead of SUM.

SUBTOTAL 9 vs 109: filtered rows vs hidden rows#

Both numbers sum, but they treat manually hidden rows differently. Rows hidden by a filter are always ignored.

So if you right-click row 3 and choose Hide, SUBTOTAL(9,...) still includes its 400, while SUBTOTAL(109,...) drops it. If you want "what I see is what I add", use 109. The SUBTOTAL function guide lists the other function numbers, such as 1 (101) for AVERAGE and 3 (103) for COUNTA.

If you format the list as an Excel table (Insert > Table, or Ctrl+T on Windows) and turn on Total Row from the Table Design tab, Excel adds a total that uses SUBTOTAL(109,...) automatically. It respects both filters and hidden rows, and you can switch it to an average or count from the drop-down in the total cell. That's often the quickest way to get a visible-only total without typing anything.

The comparison of SUBTOTAL vs SUM goes deeper into when each is the right choice. One useful extra: SUBTOTAL ignores other SUBTOTAL formulas inside its range, so group totals don't get counted twice in a grand total.

How to sum visible cells and skip errors with AGGREGATE#

AGGREGATE works like SUBTOTAL with more options, including skipping error values. It's available in Excel 2010 and later.

=AGGREGATE(9,5,C2:C8)

The first argument, 9, means SUM. The second is the option:

If one amount shows #N/A from a failed lookup, SUM and SUBTOTAL both return #N/A. =AGGREGATE(9,7,C2:C8) skips it and adds the rest of the visible cells.

How to sum visible cells with a condition#

SUMIF and SUMIFS can't tell whether a row is hidden. The simplest fix is a helper column that marks visible rows.

1. In D2, type =SUBTOTAL(103,A2) and fill it down to D8.
2. Each cell shows 1 if its row is visible and 0 if it is hidden.
3. Use the helper as an extra condition:

=SUMIFS(C2:C8,B2:B8,"North",D2:D8,1)

SUBTOTAL 103 counts non-empty cells in visible rows only, so on a single cell it returns 1 or 0. Now the North total responds to any other filter you apply, such as a date filter.

How to copy only the visible cells#

Copying a filtered range in Excel normally pastes only the visible rows. With manually hidden rows, it can paste the hidden ones too. To be sure:

1. Select the range.
2. Press Alt+; on Windows or ⌘⇧Z on Mac to select visible cells only. You can also use Home > Find & Select > Go To Special > Visible cells only.
3. Copy and paste as usual.

The guide to selecting visible cells only covers this step in more detail.

Common mistakes#

Check these if a visible total looks wrong:

1. Using SUM after filtering. Replace it with SUBTOTAL(9) or SUBTOTAL(109).
2. Using 9 when rows were hidden by hand. Switch to 109.
3. Expecting hidden columns to be skipped. SUBTOTAL only skips hidden rows.
4. Putting the total inside the filtered range. Place it below or above the data so the filter doesn't hide it.

For other ways to pick which rows get added, see how to sum every other row and how to filter and sum with formulas.

Practise it#

Filtered totals are a common trap in Excel tests: the right answer changes when the filter does. Practise filtering a table in a live spreadsheet: you apply the filter and it checks your work instantly. Then practise SUM and AutoSum, and try the intermediate Excel test when you're ready.