SUBTOTAL in Excel: Totals That Ignore Filtered Rows

The Excel SUBTOTAL function calculates a total, count or average of only the rows you can see. When you filter a list, SUM keeps adding the hidden rows, but =SUBTOTAL(109,D2:D11) adds just the visible ones and updates every time the filter changes. This guide is for anyone who filters data and wants totals that match what is on screen.

The examples use a list of 10 expense claims in A1:D11, with the columns Employee, Department, Region and Amount.

What does the Excel SUBTOTAL function do?#

SUBTOTAL runs one of 11 calculations on a range and skips rows hidden by a filter:

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

• function_num: a number that picks the calculation, such as 109 for sum.
• ref1: the range to calculate, like D2:D11. You can add more ranges after it.

It looks like SUM with an extra number in front, and with no filter on it gives the same result as SUM. The difference shows when rows are hidden. If you are new to totals, start with our SUM function guide.

SUBTOTAL function numbers: 9 vs 109#

Each calculation has two numbers. The 1 to 11 set includes rows you hid by hand. The 101 to 111 set skips them. Both sets skip rows hidden by a filter.

The ones you will use most are 109 (sum), 103 (count non-empty cells), 101 (average) and 104 (largest).

Example: total only the filtered rows#

Filter the expense list to the Sales and Marketing departments with amounts over $1,000. Four rows stay visible:

Now compare the formulas below the list:

Clear the filter and the SUBTOTAL results grow back to the full totals. For counting, use 103 on a column with a value in every row, like the names. 109 on a text column gives 0, because there are no numbers to add. Put these formulas in rows the filter can't hide, above or below the data. Our guide on how to filter data in Excel covers the filter clicks.

9 or 109: rows hidden by hand#

Choose by how rows get hidden. Filtered rows are always skipped. Rows you hide yourself, by right-clicking a row number and choosing Hide, are skipped only by the 100s.

• Use 109 for a total of "what I can see". This fits most reports.
• Use 9 if you hide rows only to tidy the view but still want them counted.

When in doubt, use 109. It always matches what is on screen.

SUBTOTAL ignores other subtotals#

SUBTOTAL skips cells in its range that hold other SUBTOTAL formulas, so a grand total never counts a group twice. Say D6 holds =SUBTOTAL(9,D2:D5) for the North group and D10 holds =SUBTOTAL(9,D7:D9) for the South group. A grand total of =SUBTOTAL(9,D2:D10) adds only the original amounts, not the two group subtotals. With SUM, the same range would add everything twice.

This is how Excel's Data > Subtotal command works. It sorts groups, inserts a SUBTOTAL row under each one and adds a grand total, all without double counting. A pivot table can do the same job with more flexibility; see our pivot tables beginner's guide and pivot table subtotals and % of total.

Where SUBTOTAL appears without you typing it#

Excel writes SUBTOTAL for you in two common places:

• AutoSum on filtered data. Click below a filtered column and press Alt+= (Mac: ⌘⇧T). Excel inserts =SUBTOTAL(9,...) instead of SUM, so the total follows the filter.
• The Total Row of an Excel table. Turn it on with Table Design > Total Row. The total cell uses =SUBTOTAL(109,[Amount]), and its drop-down switches between Sum, Average, Count and more.

If you need a list of the matching rows rather than a total, the FILTER function can return them in a new place. See FILTER function vs AutoFilter.

Quick checks without a formula#

You don't always need a formula to see a visible total. Select the Amount cells of a filtered list and look at the status bar at the bottom of the window. It shows the Average, Count and Sum of the selected cells that are visible. Right-click the status bar to add Min and Max.

The status bar is great for a quick look, but it disappears when you click elsewhere. Use SUBTOTAL when the number needs to stay on the sheet, appear in a report or feed another formula. When you type =SUBTOTAL( in a cell, Excel shows a list of every function number with its name, so you don't need to memorize the table above.

Limits and common mistakes#

• Hidden columns are not skipped. SUBTOTAL works on rows. A range across hidden columns is added in full.
• The formula inside its own range. =SUBTOTAL(109,D2:D13) typed in D13 is a circular reference. Stop the range at the last data row.
• Errors in the data. One #N/A in the range makes SUBTOTAL return #N/A. AGGREGATE can skip both hidden rows and errors: =AGGREGATE(9,7,D2:D11).
• Forgetting the number. =SUBTOTAL(D2:D11) is missing function_num. The number always comes first.

Filtering and visible totals are everyday tasks in many admin and office jobs.

Practise it#

SUBTOTAL makes the most sense when you watch it change as you filter. In ExcelReady's filter a table lesson, you filter the expense list from this guide, total the visible rows with SUBTOTAL(109, ...), count them with 103, then clear the filters and watch both numbers grow back. Try the exercise on this page, then practise SUBTOTAL with live filters.