MS Excel: SUBTOTAL Function (WS)

refer http://office.microsoft.com/en-ca/excel-help/subtotal-function-HP010062463.aspx

Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using theSubtotal command in the Outline group on theData tab. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

Syntax

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

Function_num     is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.

Function_num (includes hidden values)	Function_num (ignores hidden values)	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

Ref1, ref2     are 1 to 254 ranges or references for which you want the subtotal.

Remarks

• If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
• For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by theHide Rows command under the Hide & Unhide submenu of theFormat command in the Cells group on theHome tab. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by theHide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.
• The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
• The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
• If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1 2 3 4 5

A Data 120 10 150 23 Formula Description (Result) =SUBTOTAL(9,A2:A5) Subtotal of the column above using the SUM function (303) =SUBTOTAL(1,A2:A5) Subtotal of the column above using the AVERAGE function (75.75)