The `SUBTOTAL` function in Excel is a versatile function that allows you to perform various calculations like SUM, AVERAGE, COUNT, etc., on a range of data while ignoring any hidden rows or filtered data, depending on the function number used.
Syntax
SUBTOTAL(function_num, ref1, [ref2], ...)
- function_num: A number that specifies which function to use (e.g., SUM, AVERAGE, etc.). It ranges from 1 to 11 and 101 to 111, with 1-11 including hidden values in the calculations, and 101-111 excluding them.
- ref1, ref2, …: One or more ranges or references to cells you want to subtotal.
Functions and Their Corresponding Numbers
- 1 or 101: AVERAGE
- 2 or 102: COUNT
- 3 or 103: COUNTA
- 4 or 104: MAX
- 5 or 105: MIN
- 6 or 106: PRODUCT
- 7 or 107: STDEV
- 8 or 108: STDEVP
- 9 or 109: SUM
- 10 or 110: VAR
- 11 or 111: VARP
Examples
- Calculate the Sum of a Range Excluding Hidden Rows:
If you have a list of numbers in cells A1 to A10, and you want to sum these numbers while ignoring hidden rows:
=SUBTOTAL(109, A1:A10)
- Calculate the Average of a Range Including Hidden Rows:
If you wish to average numbers from A1 to A10 including hidden rows:
=SUBTOTAL(1, A1:A10)
- Count Non-Empty Cells Excluding Hidden Rows:
To count the number of non-empty cells in a range while ignoring hidden rows:
=SUBTOTAL(103, A1:A10)
Tips
- Filtering Data: If you use Excel’s built-in filter or hide some rows manually, `SUBTOTAL` can adjust its calculation accordingly when you use function numbers 101-111.
- Nesting Subtotals: If you’re using nested subtotals or structuring data with multiple layers of subtotals, Excel will ensure that you don’t double-count values when using `SUBTOTAL`.
When using the `SUBTOTAL` function, make sure to choose the correct function number based on whether you want to include or exclude hidden rows in your calculations.
Unlock Your Potential
Excel
Access
Power BI
