SUBTOTAL Function: syntax, examples, mistakes and translation notes
Calculates a subtotal while optionally ignoring hidden rows.
Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)
Arguments
function_num
Code identifying SUM, AVERAGE, COUNT and whether manually hidden rows are ignored.
ref1
First reference to subtotal.
ref2
Optional additional references.
Worked examples
=SUBTOTAL(9,B2:B100)
Sums visible filtered rows; function code 9 is SUM.
=SUBTOTAL(109,B2:B100)
SUM variant that also ignores manually hidden rows.
Practical tips
- Use 1–11 versus 101–111 intentionally depending on hidden-row behavior.
- SUBTOTAL automatically ignores other SUBTOTAL results inside its reference to reduce double counting.
- It is useful for filtered lists and table totals.
Common mistakes
- Using the wrong function code and getting different hidden-row behavior than intended.
- Assuming SUBTOTAL behaves exactly like SUM when nested subtotals exist.
Related functions
Frequently asked questions
What does SUBTOTAL do?
Calculates a subtotal while optionally ignoring hidden rows.
Why might a SUBTOTAL formula return an unexpected result?
Using the wrong function code and getting different hidden-row behavior than intended. Start by testing the smallest referenced range or condition independently before adding wrappers or fallbacks.
Check the complete formula
Function syntax is only one part of a working workbook. Paste the finished expression into the Formula Checker for structural warnings, the Formatter for readability or the Translator when a supported locale mapping is involved.
Testing note: examples are educational. Spreadsheet versions, locale settings, tables, named ranges and workbook data can change behavior, so validate important formulas with representative data.