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 2007

Functions: Subtotal

The Subtotal function allows you to set up a calculation in your worksheet. Then, by filtering the data, the same calculation is performed on subsets of your data. The Subtotal function ignores values in rows hidden by a filter. It can be used with Tables or any range of data.

return to topUsing the Subtotal Function

The Subtotal function is easily created with the Function Arguments dialog box.The first piece of information you will need is the function number. This determines what type of calculation will be performed.

  1. Open the desired worksheet

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

  3. From the Ribbon, select the Formulas command tab

  4. In the Function Library group, click MATH & TRIGMath & Trig button» select Subtotal
    The Function Arguments dialog box appears.
    Function Arguments dialog box

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

  6. In the Ref1 text box, type the range of cells to be analyzed
    OR
    1. Click COLLAPSE DIALOGCollapse Dialog box button
    2. Select the range of cells to include
    3. Click RESTORE DIALOGRestore Dialog button

  7. Repeat step 6 for as many different cell ranges as necessary
    NOTE: You may select up to 29 different fields to include in the subtotal.

  8. Click OK
    The subtotal appears in the selected cell and the formula appears in the Formula Bar.
    EXAMPLE: In the formula =SUBTOTAL(1,D2:D11), 1 designates the calculation from the Function_num text box as an average, and D2:D11 designates the cell range from the Ref1 text box.

return to topUsing a Filter to Analyze Subsets

If you have applied the Subtotal function to a section of your worksheet and want to see results for a specific category of information contained within that subtotal, you can hide rows by filtering the field. After filtering, the function will calculate with only the rows that are visible, allowing you to see customized results. For more information on filtering, refer to Filtering Your Database.

  1. Select the row of column headings

  2. From the Ribbon, select the Data command tab

  3. In the Sort & Filter group, click FILTERFilter button
    Pull-down lists appear in each of the selected cells.
    Filtering a database

  4. 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's pull-down list, select Sophomore.
    The worksheet hides all other cells except those meeting the selected Filter criteria. The new average is shown in the same cell where the first subtotal appeared.

  5. To return to the unfiltered view, from the Sort & Filter group, click FILTERFilter button

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 value
    HINT: Choose from the list of possible calculations.

  3. Press [Enter]

return to topFunction Numbers

The Subtotal function allows you to work several different calculations on your selected data. In order for the function to work correctly, you will need to use the number of the correct calculation when prompted.

# 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 topAn Example

The example below shows data about employees' time worked in particular pay periods. With this unfiltered data, the Subtotal function used at the bottom of the Hours column reflects the maximum number of hours worked by any employee.
Example of an unfiltered table using the Subtotal function

The image below shows the results of filtering the data. After applying a filter so that only Justin's information is visible, the Subtotal function calculates the maximum hours that Justin worked.
Example of a filtered table using the Subtotal function

Excellence. Our Measure. Our Motto. Our Goal.