Microsoft Office Tutorials and References
In Depth Information
The SUBTOTAL function returns a subtotal in a list or database. You can apply this function to a
data table automatically by using the Subtotals command (Data menu). After the subtotal list
is created, you can modify it by editing the SUBTOTAL function.
The capability of the SUBTOTAL function to return information from a filtered list makes it one
of the most powerful of Excel’s functions.… If there are other subtotals within ref1,
ref2,... (or nested subtotals), these nested subtotals are automatically ignored. SUBTOTAL
will ignore any hidden rows that result from a list being filtered. This is important when you
want to subtotal only the visible data that results from a list that you have filtered. If any of
the references are 3D references, SUBTOTAL returns an error value.
The SUBTOTAL function can be used either with the normal filter (available by selecting Data,
Filter from the menu) or by using the advanced filter (Data, Filter, Advanced Filter). The
example shown in Figure 10.24 uses the advanced filter. In this case the data table has been
filtered to show only unique records. If the status bar is visible during this operation, the
number of unique records will be displayed there. However, to return that number to the
worksheet, the SUBTOTAL function with a first argument of 3 gives the number of unique items
in cell B28.
FNUM The FNUM argument is a number from 1 to 11 that specifies
which function to use in calculating subtotals within a list.
There can be up to 29 ref arguments.
CELL REFERENCE or RANGE The cell or range in which to reference.
Where the FNUM arguments are as follows: