ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

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 non-blank 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 1999-08-14 12:44:13      Printerfriendly version

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2024    Ole P. Erlandsen   All rights reserved
E-mail Contact Address