|
|
SUBTOTAL Function Definition
This function performs a specified calculation
on a range or group of ranges and cells. It can perform numerous calculations
(average, count, sum...) and has the capabilities of ignoring
hidden rows and columns which makes it ideal for using with the
AutoFilter.
SUBTOTAL Function Syntax
=SUBTOTAL(function_num,
ref1, [ref2], ...)
SUBTOTAL Function Argument Definition
function_num:
This 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: These arguments can be cell or range references.
There can be 1 to 29 ranges or references for which you want the subtotal.
SUBTOTAL Function Tips
- 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 the
Hide command under the Row submenu of
the Format menu). 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 the Hide command under the Row
submenu of the Format menu). 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.
SUBTOTAL Function Example
- Click here to view SUBTOTAL function
examples
|
