This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2003/2004

Functions: Subtotal

The Subtotal function sections off a part of the worksheet so that calculations can be performed upon it. There are 11 different calculations which you can have performed on the subset of the worksheet, as shown in the Function Numbers table. You must select one of these calculations to be initially performed on the subset. You may, however, change the selection at any time by modifying the existing function if you need to perform other calculations on the data.

return to topFunction Numbers

# Calculation Description
1 AVERAGE adds all entries and then divides by the number of entries
2 COUNT counts the number of entries containing numbers
3 COUNTA counts the number of entries that are not blank (includes text entries)
4 MAX reports the highest number of all the entries
5 MIN reports the lowest number of all the entries
6 PRODUCT multiplies all the entries together
7 STDEV computes the standard deviation, assuming the selection is a sample of the entire population
8 STDEVP computes the standard deviation, assuming the selection is the entire population
9 SUM adds all entries together
10 VAR computes the variance, assuming the selection is a sample of the entire population
11 VARP computes the variance, assuming the selection is the entire population

return to topUsing the Subtotal Function

The Subtotal function will ignore any hidden rows within your worksheet. This becomes a factor if you filter your worksheet. Filtering your worksheet after performing the Subtotal function will provide you with subtotals on only the visible filtered rows. This is a handy way to obtain subtotal calculations of subsets within your data. For more information on filtering, refer to Filtering Your Database.

  1. Open the desired worksheet

  2. Select the cell where you want the result of the function to appear
    NOTE: If filtering your worksheet, make sure that the selected field is below your data, allowing you to see the results.

  3. From the Insert menu, select Function...
    OR
    Windows: On the Formula Bar, click INSERT FUNCTIONInsert Function button
    The Insert Function dialog box appears.
    Macintosh: On the Standard toolbar, click PASTE FUNCTIONPaste Function button
    The Paste Function dialog box appears.

  4. Windows: From the Or select a category pull-down list, select All or Math & Trig
    Macintosh: From the Function category scroll box, select All or Math & Trig

  5. Windows: From the Select a function scroll box, select SUBTOTAL
    Macintosh: From the Function name scroll box, select SUBTOTAL

  6. Click OK
    The Function Arguments dialog box appears.
    Function Arguments dialog box

  7. In the Function_num text box, type the number of the function you would like to perform
    NOTE: For more information, refer to Function Numbers.

  8. In the Ref1 text box, type the range of cells
    OR
    1. Click COLLAPSE DIALOGCollapse Dialog box buttonorCollapse Dialog box button
    2. Select the range of cells to include
    3. Click RESTORE DIALOGRestore Dialog buttonorExpand Dialog box button
  9. Repeat step 8 for as many of the Ref text boxes as necessary
    NOTE:You may select up to 29 different fields to include in the subtotal.

  10. Click OK
    The subtotal appears in the selected cell and the formula appears in the Formula Bar.
    HINT: The formula should look similar to =SUBTOTAL(1,D2:D11) with 1 (average) being the Function_num, and D2:D11 being Ref1.

return to topFiltering

After you have your subtotal on a field in your worksheet, you can easily get the same information on subsets of that same field. You can do this by filtering the field. For more information on filtering, refer to Filtering Your Database.

  1. Select the row of column headings, such as name, class, or age

  2. From the Data menu, select Filter » AutoFilter
    Pull-down lists appear in each of the selected cells.
    filtering a database

  3. From the desired pull-down list, select the desired option
    EXAMPLE: If you wanted to find the average age of "Sophomores," from the class cell pull-down list, select Sophomore
    The worksheet hides all other cells except those meeting the selected AutoFilter criteria. The new average is shown in the same cell where the first subtotal appeared.

  4. To return to the unfiltered view, from the Data menu, select Filter » AutoFilter

return to topModifying the Subtotal Function

Once you perform one of the Subtotal calculations on your data, you can change the calculation at any time by modifying the existing function. This allows you to perform additional calculations on the same data.

  1. Select the cell where the Subtotal function is being performed
    The function appears in the Formula Bar.

  2. On the Formula Bar, change the Function_num
    HINT: Choose from the list of possible calculations.

  3. Press [Enter] or [return]

Excellence. Our Measure. Our Motto. Our Goal.