SUBTOTAL() Function to Summarize Data

I learned about this formula fairly recently as a better alternative to using the SUM() function when summarizing multiple totals in a single data column.

THE NEED:
Adding Subtotals or Totals to a table when there are multiple items.  This function is particularly useful because it can create a column grand total that ignores other subtotals.  I recently used this to calculate (1) the area of dense vegetation I was planning to plant, (2) the area of scattered (buffer) vegetation that was in my project site, and (3) total area being restored.  I have also used it to create grant budget tables.

FORMULA USE:

The screen capture image below shows how I used three SUBTOTAL() functions:  Two to calculate subtotals for each treatment type and one grand total.

Note that three SUM() functions could have been used and for the two treatment subtotals the area referenced would be the same [e.g. SUM(D2:D9) to summarize square meters].  However, if I had used SUM(D2:D17) for the Total, it would have included the subtotals in D10 and D17 and way over-inflated the data totals.  The SUBTOTAL() function adds all numbers in the column (or other referenced aera) and ignores other SUBTOTAL() functions.

One benefit to this is that new data or treatments can be added without having to re-reference the formula in the TOTAL row.  The function "=SUM(D17,D10)" would result in the same Total figure, but if additional categories or treatments were created, you would need to update the SUM function to include new subtotal cells, whereas the SUBTOTAL() function would not have to be changed.

In writing grants or managing budget tables, the SUBTOTAL() function is also very useful to keep track of multiple budget category subtotals plus a grand total figure at the bottom.

ADDITIONAL NOTES:

Finally, the first operator in the parenthesis in my example is a "9" which tells Excel to summarize all numbers (except subtotals) in the referenced cells (D2 to D17).  However, use the following other codes to do other calculations on your data:

1: Average

2: Count (counts non-blank cells and arguments with numbers)

3: CountA (counts non-blank cells plus other non-blank arguments)

4: Max (reports the highest number in referenced area)

5: Min

6: Product

7: STDEV

8: STDEVP

9: SUM

10: VAR

11: VARP