Microsoft Office Tutorials and References

In Depth Information

**Summarizing worksheets by using hidden and filtered rows**

Operation number

(ignores values in

manually hidden

rows)

Operation number

(includes hidden

values)

Function

Description

7

107

STDEV.S

Calculates the standard

deviation of the values in

the range by examining a

sample of the values

8

108

STDEV.P

Calculates the standard

deviation of the values in

the range by using all the

values

9

109

SUM

Returns the result of

adding all numbers in the

range together

5

10

110

VAR.S

Calculates the variance of

the values in the range by

examining a sample of the

values

11

111

VAR.P

Calculates the variance of

the values in the range by

using all of the values

As the previous table shows, the
SUBTOTAL
function has two sets of operations. The first

set (operations 1 through 11) represents operations that include hidden values in their

summary, and the second set (operations 101 through 111) represents operations that

summarize only values visible in the worksheet. Operations 1 through 11 summarize all

cells in a range, regardless of whether the range contains any manually hidden rows. By

contrast, operations 101 through 111 ignore any values in manually hidden rows. What the

SUBTOTAL
function doesnâ€™t do, however, is change its result to reflect rows hidden by using

a filter.

The
AGGREGATE
function extends the capabilities of the
SUBTOTAL
function. With it, you

can select from a broader range of functions and use another argument to determine

which, if any, values to ignore in the calculation.
AGGREGATE
has two possible syntaxes,

depending on the summary operation you select. The first syntax is
=AGGREGATE(function_

num, options, ref1...)
, which is similar to the syntax of the
SUBTOTAL
function. The other

possible syntax,
=AGGREGATE(function_num, options, array, [k])
, is used to create
AGGREGATE

functions that use the
LARGE
,
SMALL
,
PERCENTILE.INC
,
QUARTILE.INC
,
PERCENTILE.EXC
,

and
QUARTILE.EXC
operations.