Recent Addins ADO Application Arrays Charts Commandbars Count Dates Dialogs Events File access Files & Folders Functions Games Help Import & Export Lookup Off topic Other Outlook PivotTables Power Query Printing Problem solving Read me Searching Sorting Sound Strings Sum Time VBA programming VBE Workbooks Worksheets

Subtotals

 1999-08-14    Sum    0    196

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 the formula below:
=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:

Number 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