For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Math and trigonometry function

SUBTOTAL Function

Returns a subtotal of the visible (unfiltered) items in a list or database


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Syntax

SUBTOTAL(function_num, ref1, ref2, ...)

  • function_num Required. As shown in the following table, the number 1 to 11 (to include hidden values) or 101 to 111 (to ignore hidden values), which specifies the function to use in calculating subtotals within a list.
  • ref1  Required. The first named range or reference for which you want the subtotal.
  • ref2, ...  Optional. Named ranges or references 2 to 254 for which you want the subtotal.

(Includes Hidden Values)
FUNCTION_NUM
(Ignores Hidden Values)
FUNCTION_NUM
FUNCTION
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Applies To

Excel 2003 and above

Remarks

The SUBTOTAL function ignores filtered data but not data in rows that were hidden manually. In contrast, when the AGGREGATE function is set to ignore data in hidden rows, it ignores data in all hidden rows, not just in rows that were hidden by filters.

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

Examples for Excel's SUBTOTAL function

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards