How to Use Excel's SUBTOTAL Function

By Techwalla Computers Editor

Excel's SUBTOTAL function calculates a subtotal from a database or list. It is normally used only to modify an existing list because the Subtotals command from the Data menu can usually create the list more easily. The following steps will show how to use Excel's SUBTOTAL function.

Step 1

Learn the syntax for SUBTOTAL. It is SUBTOTAL(function_number, reference_1, reference_2,...reference_i...,reference_n) where function_number is the number corresponding to the function to use for calculating the subtotal and reference_i are up to 29 references for which the subtotal will be calculated.

Step 2

Examine the following function numbers and their corresponding functions: 1, AVERAGE; 2, COUNT; 3, COUNTA; 4, MAX; 5, MIN; 6, PRODUCT; 7, STDEV; 8, STDEVP; 9, SUM; 10, VAR; and 11, VARP. Note that these function numbers will include hidden values. Add 100 to these function numbers if you want the function to ignore hidden values.

Step 3

Expect nested subtotals within the references to be ignored by the SUBTOTAL function to avoid counting them twice. Rows that are not included in a filter's result also will be ignored. SUBTOTAL will return the #VALUE! error value when any reference is a 3-D reference.

Step 4

Enter the following values into the first column of an Excel spreadsheet:Row 1: Data;Row 2: 110;Row 3: 15;Row 4: 165.

Step 5

Look at some examples of SUBTOTAL based on the entries made in Step 4: =SUBTOTAL(9,A2:A4) will evaluate as SUM(110,15,165) or 290 because function 9 is the SUM function; =SUBTOTAL(1,A2:A4) will evaluate as AVERAGE(110,15,165) or about 96.67 because function 1 is the AVERAGE function.