### Subtotals

1999-08-14 Sum 0 57

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 |