Microsoft Office Tutorials and References
In Depth Information
SUM VISIBLE ROWS
any of 11 operations. The ﬁ rst parameter indicates Average (1), Count (2),
CountA (3), Max (4), Min (5), Product (6), StdDev (7), StdDevP (8), Sum (9),
Var (10), or VarP (11). When you add 100 to this parameter, Excel includes
only visible cells in the result.
In Figure 42, you can see that the result of the SUM in row 565 and the result of
the SUBTOTAL(9 , in row 567 are identical. When you switch to SUBTOTAL(109,
in row 566, Excel total only the visible cells in the range.
Figure 42. h
e 100 series of SUBTOTAL functions sum, average, and count only the visible rows.
Gotcha: There is an error in Excel Help. The Help topic says that the 100
series parameters sum only visible cells. This is true only of cells that are in
hidden rows. If your data is hidden due to hiding a column, Excel still includes
those cells (Figure 43).
Figure 43. h
e formula fails to ignore cells hidden using hidden columns.
Additional Details: There is an unusual exception to the behavior of the
SUBTOTAL function. When your rows have been hidden by any of the Filter
commands (Advanced Filter, AutoFilter, or Filter), Excel includes only the visible
rows in a SUBTOTAL(9 , function. There is no need to use the 109 version.
In Figure 44, Advanced Filter is used to ﬁ nd only the AT&T records for two
products. The regular SUBTOTAL with an argument of 9 works ﬁ ne to sum only
the visible rows.