Saturday, November 06, 2004

Subtotal in Excel

A very useful function in Excel is the SUBTOTAL function. Using this in place of SUM in lists with subtotals in it allows for accurate summing of the entire list without having to link individual subtotals.

For example you may have a list of 10 values. The first 5 are subtotalled below in the same columm using the SUM function. The second 5 are also subtotalled using SUM. To get a grand total at the bottom you will need to add the two subtotal cells. (See the first image).
Using the SUBTOTAL function instead (option 9) will allow you to select the entire range and the command automatically ignores other subtotals. (See the second image)

A full list of the options available using the SUBTOTAL function can be found in the help. Reprinted here for your convenience.

Function_num(includes hidden values)
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Function_num (ignores hidden values - only availble in Office 2003)
101 AVERAGE
102 COUNT
103 COUNTA
104 MAX
105 MIN
106 PRODUCT
107 STDEV
108 STDEVP
109 SUM
110 VAR
111 VARP

So using a function number in the SUBTOTAL function tells Excel to use that function in the formula result. Eg 4 will return the maximum value in the list.


Posted by: Jethro | 9:46 AM |