These pages are no longer updated and are only available for archive purposes.
Click here to visit the pages with updated information.
Subtotals
If the range A1:A1000 contains values and several subtotals, you probably will
have some difficulties to add the grand total of the values in this range. One easy way to
find the grand total is by using this formula :
=SUM(A1:A1000)/2
This solution assumes that all the subtotals adds all the items in the range.
Another way to find the grand total is by using the SUBTOTAL(functionnumber,cellref)function
when you add the subtotals in the range. To find the grand total you can type in this
formula :
=SUBTOTAL(9,A1:A1000)
The SUBTOTAL()function will ignore the result from all other SUBTOTAL()functions.
Note! The SUBTOTAL()function will only calculate visible cells.
Function numbers and SUBTOTAL functions :
No.: 
Function : 
Returns : 
1 
AVERAGE 
average of the values in the range 
2 
COUNT 
count of cells containing values in the range 
3 
COUNTA 
count of nonblank cells in the range 
4 
MAX 
the maximum value in the range 
5 
MIN 
the minimum value in the range 
6 
PRODUCT 
multiplies the values in the range 
7 
STDEV 
standard deviation for samples of the values in the range 
8 
STDEVP 
standard deviation for populations of the values in the range 
9 
SUM 
the sum of the values in the range 
10 
VAR 
variance for samples of the values in the range 
11 
VARP 
varians for populations of the values in the range 
Document last updated 19990814 12:44:13
