March 2nd, 2005
n SUBTOTAL function allows you to perform basic spreadsheet operations on the visible cells in AutoFilter mode.
In the example below, we have sales figures for 4 salesmen - George, Paul, Ringo and John.
After we apply the AutoFilter to display only the sales related to Paul, we use SUBTOTAL as shown to average his sales.
The SUBTOTAL function takes two arguments:
SUBTOTAL(function; range)
Function is a number that stands for one of the following functions:
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
If you do not wish to use AutoFilter, you can use a combination of SUMIF and COUNTIF as shown below to achieve the same results - at least as far as averaging is concerned.
