Microsoft Office Tutorials and References
In Depth Information
Figure 9-25: Using the DSUM function to sum a table using a criteria range.
Subtotal command is a handy tool that inserts formulas into a worksheet
database automatically. These formulas use the SUBTOTAL function. To use this feature, your
database must be sorted because the formulas are inserted whenever the value in a specified
field changes. For more information about the SUBTOTAL function, refer to the sidebar, “About
the SUBTOTAL function,” earlier in this chapter.
When a table is selected, the Data
Subtotal command is not available.
Therefore, this section applies only to worksheet databases. If your data is in a table
and you need to insert subtotals automatically, convert the table to a range by using
Table Tools➜Design➜Tools➜Convert To Range. After you insert the subtotals, you can
convert the range back to a table by using Insert
Figure 9-26 shows an example of a range that is appropriate for subtotals. This database is
sorted by the Month field, and the Region field is sorted within months.
This workbook, named nested subtotals.xlsx , is available on the companion
To insert subtotal formulas into a worksheet database automatically, move the cell pointer
anywhere in the range and choose Data
Subtotal. You will see the Subtotal dialog box, as
shown in Figure 9-27.