SUBTOTAL Function

Summary

The Excel SUBTOTAL function performs a calculation such as SUM, AVERAGE, COUNT, MAX, or MIN on a list or database. Its distinguishing feature is that it can ignore rows hidden by filtering, which makes it useful for totals that need to respond to the current visible view of the data.

SUBTOTAL also avoids double-counting other SUBTOTAL formulas inside the referenced range. That makes it suitable for reports with several subtotal lines and a larger total that should still be based on the underlying data rather than on intermediate subtotal rows.

SUBTOTAL is useful in list-style models because it can respond to filtered and visible data. That gives it a different role from a plain total: it helps worksheets stay interactive, especially when the visible slice of the data changes over time.

Syntax

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

The first argument selects the type of calculation. Microsoft documents two code groups: 1-11 and 101-111. Both exclude filtered-out rows, but the 100-series also excludes rows hidden manually.

Arguments

• function_num - [required] A code that selects the calculation to perform.
• ref1 - [required] The first range to evaluate.
• ref2, ... - [optional] Additional ranges, up to 254 references total.

Common Function Codes

Using SUBTOTAL

A common use of SUBTOTAL is a filter-aware total row. If a report is filtered by region, product, or status, a normal SUM continues to calculate all rows in the range, including the ones no longer visible. SUBTOTAL recalculates against the visible set instead, which is why it is a standard choice in filtered reports.

The function number is not a minor detail. It determines both the type of aggregation and, depending on whether you use the 1-11 group or the 101-111 group, whether manually hidden rows should still be counted. That distinction matters in worksheets where some rows are hidden for presentation rather than by filter logic.

SUBTOTAL also has an architectural advantage in layered reports: nested SUBTOTAL results are ignored. If a range already contains subtotal rows, a higher-level SUBTOTAL over that range still reads the underlying data correctly. That behavior helps prevent accidental double-counting in structured worksheet summaries.

Example 1 - Sum the Visible Rows

This is the most common use of SUBTOTAL.

=SUBTOTAL(9,B1:B5)

Code 9 means SUM. If all rows are visible, the result matches a normal SUM. Once a filter is applied, the result changes to reflect only the visible rows. That is the main reason to choose SUBTOTAL over SUM in filter-driven reports.

Example 2 - Count Visible Non-Empty Cells

SUBTOTAL is not limited to sums.

=SUBTOTAL(3,B1:B5)

Code 3 means COUNTA, so the result is the number of visible non-empty cells in the range. This is useful for report headers, filtered record counts, and quick audit summaries.

Example 3 - Average the Current View

The visible rows can be treated as a working subset of the data.

=SUBTOTAL(1,B1:B5)

Code 1 means AVERAGE. If a filter narrows the list to a subset of rows, the average reflects that subset rather than the full range. That makes SUBTOTAL useful for interactive reports where the filter state changes frequently.

Example 4 - Ignore Manually Hidden Rows Too

The 100-series codes change hidden-row behavior.

=SUBTOTAL(109,B1:B5)

Code 109 also performs SUM, but unlike code 9 it excludes rows hidden manually as well as rows hidden by filtering. This version is more suitable when hidden rows should be fully excluded from the reported result.

SUBTOTAL is used automatically by the Total Row feature in Excel Tables. That is a good reminder that the function is not just a formula trick; it is part of how Excel itself handles visible-row summaries.

• Codes 1-11 include manually hidden rows.
• Codes 101-111 exclude manually hidden rows.
• Filtered-out rows are excluded in both code groups.

Conclusion Recap

SUBTOTAL is most useful when the worksheet view can change. This lesson showed that unlike a normal SUM or AVERAGE, SUBTOTAL can follow the visible rows, which makes it a better fit for filtered reports and table summaries.

The other important idea was the function code. It does two jobs at once: it chooses the type of calculation and it decides how hidden rows are treated. Once you understand that part, SUBTOTAL becomes much easier to use correctly.

• Summary: SUBTOTAL aggregates data while respecting filtered rows.
• Main advantage: It adapts to the visible state of the dataset.
• Important detail: The function code controls both the aggregation type and hidden-row behavior.
• Reporting benefit: Nested SUBTOTAL formulas are ignored automatically.
• Common use: Filter-aware totals, counts, and averages in reports and tables.