Microsoft Office Tutorials and References
In Depth Information
In its default use, SUBTOTAL works just like the SUM function, except it
throws out other instances of the SUBTOTAL function within the range being
summed. The SUBTOTAL function takes the following arguments:
function_num This is a number from 1 to 11. The most common func-
tion number is the number 9, which (for no apparent logical reason) is
used to sum. When Microsoft introduced the SUBTOTAL function, it
offered 11 options: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT,
STDEV, STDEVP, SUM, VAR, and VARP. It just happens that SUM is the
ninth item in this list when these functions are arranged alphabetic-
ally in the English language, so 9 became the function number for
SUM. Although SUBTOTAL always ignores rows hidden as the result
of a filter, it does not automatically ignore rows hidden with the HIDE
command. To ignore rows that have been manually hidden, add 100 to
the function_num.
ref1,ref2,... These are up to 254 ranges or references that you want
to subtotal. Unlike with SUM, the references in a SUBTOTAL function
cannot be 3-D references.
Any other nested subtotals in the range are ignored to prevent double count-
The SUBTOTAL function always ignores rows hidden as the result of a fil-
ter. This makes the SUBTOTAL function great in combination with filter, as
you ll see later in this chapter, in Figure 11.19 .
A feature added in Excel 2002 is that you can add 100 to the function number to
prevent Excel from including rows hidden by using the Hide command. Note that
this functionality works only with hidden rows. If you hide columns and at-
tempt to subtotal in a horizontal fashion, the hidden columns are not ignored.
The arguments for SUBTOTAL are shown in Table 11.6 .
Search JabSto ::

Custom Search