What's the difference between SUBTOTAL and SUM?

SUM adds all cells in a range regardless of visibility; SUBTOTAL ignores manually hidden rows and nested SUBTOTAL functions. Use SUBTOTAL when working with filtered or intentionally hidden data where you only want to calculate visible rows.

What do the function numbers 1–11 versus 101–111 mean?

Numbers 1–11 include hidden rows in the calculation; 101–111 exclude them. For example, both SUBTOTAL(9, D:D) and SUBTOTAL(109, D:D) sum numeric values, but 109 ignores rows you've manually hidden, while 9 includes them. Use 101–111 for filtered datasets.

Can SUBTOTAL work with text data?

Yes, but only with text-appropriate functions. COUNTA (function 3) counts non-empty cells including text; COUNT (function 2) counts only numbers. Arithmetic functions like SUM (9) or AVERAGE (1) return #VALUE! if applied to text.

Why is SUBTOTAL better than manually deleting rows?

SUBTOTAL lets you toggle visibility on and off without losing data. If you delete a row and later need it, it's gone. Hiding rows preserves the data, and SUBTOTAL respects that invisibility—perfect for temporary filtering or audit trails where hidden rows must remain retrievable.

SUBTOTAL function

Returns a subtotal of numeric values in a range, optionally excluding hidden rows and nested SUBTOTAL functions.

=SUBTOTAL(function_num, ref1, ...)

Generate a SUBTOTAL formula

Describe what you need. The generator will reach for SUBTOTAL where SUBTOTAL is the right tool, and tell you when it is not.

Arguments

How SUBTOTAL reads its arguments

Argument	Required	Description
function_num	Required	A number from 1–11 (includes hidden rows) or 101–111 (excludes hidden rows); determines which aggregation (SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, VAR, STDEV) to apply.
ref1	Required	A range or cell reference containing numeric values (or text for COUNTA/COUNT); the primary data to aggregate.
...	Repeating	Additional ranges or references to include in the same aggregation; all arguments follow the same rules as ref1.

Returns

A number, either the sum, count, average, maximum, minimum, product, variance, or standard deviation of the input values depending on function_num.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Sum all visible assignment scores across the class

Student	Subject	Assignment	Score	Max
Alice	Math	Quiz 1	18	20
Alice	Math	Midterm	85	100
Bob	English	Quiz 1	16	20
Bob	English	Final	78	100
Charlie	Science	Quiz 1	20	20
Charlie	Science	Final	95	100

=SUBTOTAL(9, D2:D7)

Result: 312

SUBTOTAL with function 9 sums column D (Score). If any rows are manually hidden—for example, if Alice's Midterm was marked as withdrawn and hidden—those scores are automatically excluded from the total, yielding a smaller result. This is SUBTOTAL's key strength over SUM.

2. Find the highest visible score

Student	Subject	Assignment	Score	Max
Alice	Math	Quiz 1	18	20
Alice	Math	Midterm	85	100
Bob	English	Quiz 1	16	20
Bob	English	Final	78	100
Charlie	Science	Quiz 1	20	20
Charlie	Science	Final	95	100

=SUBTOTAL(4, D2:D7)

Result: 95

SUBTOTAL with function 4 finds the maximum value in the Score column. Charlie's Science Final (95) is the highest grade. If that row were hidden, SUBTOTAL(4, ...) would return 85 instead, respecting the hidden rows.

3. Count how many assignment records are visible

Student	Subject	Assignment	Score	Max
Alice	Math	Quiz 1	18	20
Alice	Math	Midterm	85	100
Bob	English	Quiz 1	16	20
Bob	English	Final	78	100
Charlie	Science	Quiz 1	20	20
Charlie	Science	Final	95	100

=SUBTOTAL(3, D2:D7)

Result: 6

SUBTOTAL with function 3 (COUNTA) counts non-empty cells in visible rows. All 6 scores are shown, so the result is 6. If two rows were filtered or hidden, the count would drop to 4, making this useful for auditing how many submissions are currently visible.

Common errors

Which SUBTOTAL error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	function_num is outside the range 1–11 or 101–111. For example, =SUBTOTAL(50, D2:D7) uses an invalid function number.	Use a valid function number: 1–11 for operations including hidden rows, or 101–111 to exclude them (e.g., 9 for SUM, 4 for MAX, 3 for COUNTA).
#VALUE!	function_num is supplied as text instead of a number. For example, =SUBTOTAL("SUM", D2:D7) passes the word SUM rather than its numeric code 9.	Replace text function names with their numeric equivalents: use 9 instead of "SUM", 1 instead of "AVERAGE", 4 instead of "MAX".
#VALUE!	A numeric aggregation function (like 9 for SUM) is applied to text data. For example, =SUBTOTAL(9, A2:A7) tries to sum student names instead of scores.	Ensure the range contains numeric values matching the function. Use COUNTA (function 3) for text, or reference a numeric column like the Score column instead of the Student column.

Tips and when to use something else

Use functions 1–11 to include hidden rows in calculations, or 101–111 to exclude them. The 100+ series is most common when filtering data.
SUBTOTAL automatically ignores other SUBTOTAL functions nested within it, preventing double-counting in filtered ranges.
For conditional aggregation (summing only rows where a condition is true), use SUMIF or SUMIFS instead of SUBTOTAL, since SUBTOTAL doesn't evaluate conditions.
SUBTOTAL is ideal when rows are manually hidden or filtered; for general summation of complete datasets, SUM is simpler and clearer.

Related functions

SUMSUM adds numeric values in a range or multiple ranges, ignoring text and empty cells, and returns the total sum.SUMIFSUMIF returns the sum of cells in a range that meet a specified criteria, useful for totaling data filtered by a single condition.SUMIFSSUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.AGGREGATEReturns the result of a function (like SUM, AVERAGE, COUNT) applied to a range, with built-in options to ignore error values, hidden rows, and nested SUBTOTALs.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.

Frequently asked questions

What's the difference between SUBTOTAL and SUM?: SUM adds all cells in a range regardless of visibility; SUBTOTAL ignores manually hidden rows and nested SUBTOTAL functions. Use SUBTOTAL when working with filtered or intentionally hidden data where you only want to calculate visible rows.
What do the function numbers 1–11 versus 101–111 mean?: Numbers 1–11 include hidden rows in the calculation; 101–111 exclude them. For example, both SUBTOTAL(9, D:D) and SUBTOTAL(109, D:D) sum numeric values, but 109 ignores rows you've manually hidden, while 9 includes them. Use 101–111 for filtered datasets.
Can SUBTOTAL work with text data?: Yes, but only with text-appropriate functions. COUNTA (function 3) counts non-empty cells including text; COUNT (function 2) counts only numbers. Arithmetic functions like SUM (9) or AVERAGE (1) return #VALUE! if applied to text.
Why is SUBTOTAL better than manually deleting rows?: SUBTOTAL lets you toggle visibility on and off without losing data. If you delete a row and later need it, it's gone. Hiding rows preserves the data, and SUBTOTAL respects that invisibility—perfect for temporary filtering or audit trails where hidden rows must remain retrievable.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17