Subtotal Function

Subtotals are extremely useful when designing spreadsheets that require multiple totals on the same data. The "Subtotal()" function is much more flexible and useful than the "Sum()" function. The subtotal function can sum, count, average, and find the min or max of a selected data range. The nice attribute of the subtotal function is that it will not include in the total a cell that contains the subtotal function. For example,
Cell A1: 1
Cell A2: 4
Cell A3: 5 = subtotal(A1:A2)
Cell A4: 7
Cell A5: 12 = subtotal(A1:A4)

You see that the cell A5 refers to the entire range of data, but does not double count cell A3. This is convenient because it makes your formulas easier to construct and read.

In fact, I love this function so much, I created a short video demo...you should check it out.  Excel Subtotal Video.  The video first appeared in our firm's October 2008 newsletter, and is about a minute or so in length.  If you have never played with the subtotal function, this video is a pretty easy way to see how it is done and how it can be used.

Subtotal Function