Excel SUBTOTAL function

Summary

The Excel SUBTOTAL function returns an aggregate result for supplied values. This function can perform various operations like SUM, AVERAGE, COUNT, MAX, and more. It’s especially useful for calculating subtotals in a range that contains hidden rows or columns.

Syntax
				
					=SUBTOTAL(function_num, ref1, [ref2], ...)
				
			
  • function_num: A number that specifies the function to use for the subtotal (see the list below for a list of function numbers)
  • ref1, [ref2], …: One or more cell ranges to calculate the subtotal for
Return value
A subtotal based on the specified function and range.

How to use

SUBTOTAL is used to calculate subtotals in a list or database. It can perform different types of calculations, including sum, average, count, etc. This function is particularly useful in filtering data, as it only includes visible cells in calculations.

function_num values:

  • 1: AVERAGE
  • 2: COUNT
  • 3: COUNTA
  • 4: MAX
  • 5: MIN
  • 6: PRODUCT
  • 7: STDEV.S
  • 8: STDEV.P
  • 9: SUM
  • 10: VAR.S
  • 11: VAR.P
  • 101: AVERAGE (ignoring hidden rows)
  • 102: COUNT (ignoring hidden rows)
  • 103: COUNTA (ignoring hidden rows)
  • 104: MAX (ignoring hidden rows)
  • 105: MIN (ignoring hidden rows)
  • 106: PRODUCT (ignoring hidden rows)
  • 107: STDEV.S (ignoring hidden rows)
  • 108: STDEV.P (ignoring hidden rows)
  • 109: SUM (ignoring hidden rows)
  • 110: VAR.S (ignoring hidden rows)
  • 111: VAR.P (ignoring hidden rows)

Examples

Simple SUBTOTAL
Subtotal for Summation: Calculating the sum of a range:
				
					=SUBTOTAL(9, A2:A10)
				
			
This formula sums up the values in A2:A10.
SUBTOTAL for Averaging
Average of Visible Cells: Finding the average of a range, excluding hidden cells:
				
					=SUBTOTAL(1, B2:B10)
				
			
This formula calculates the average of visible cells in B2:B10.
SUBTOTAL with Filters
Subtotal with Filtered Data: Summing filtered (visible) data in a range:
				
					=SUBTOTAL(109, C2:C20)
				
			
In a filtered list, this formula sums only the visible (non-hidden) cells in C2:C20.
SUBTOTAL for Counting
Counting Visible Cells: Counting the number of visible cells with numeric data:
				
					=SUBTOTAL(102, D2:D100)
				
			
Counts the number of visible numeric entries in D2:D100.
SUBTOTAL for Advanced Data Analysis
Dynamic Subtotals in Data Analysis: Creating dynamic subtotals that adjust with data filtering:
				
					=Principal * (1 + InterestRate) ^ SQRT(Time)a=SUBTOTAL(101, E2:E200)
				
			
In this example, the average of E2:E200 is calculated, adjusting dynamically as rows are hidden or shown.

Additional Notes

  • SUBTOTAL is ideal for use in tables where rows may be hidden, as it automatically adjusts to calculate only visible data.
  • Using function numbers from 101-111 makes SUBTOTAL ignore hidden rows, useful in filtered lists.

Related Functions

Excel AGGREGATE function

The Excel AGGREGATE function performs calculations like SUM, AVERAGE across lists, ignoring errors or hidden rows, enhancing data analysis.

Content Navigation