Subtotal Function

The SUBTOTAL function can return an SUM, AVERAGE, COUNT, MAX, and other values, and it can include or exclude values from hidden rows.

Syntax =SUBTOTAL (function_num, ref1, [ref2], …)

Arguments 

• function_num – A number that specifies which function to use in calculating subtotals within a list. See table below for full list.
• ref1 – A named range or reference to subtotal.
• ref2 – [optional] A named range or reference to subtotal.

The SUBTOTAL function in Excel returns an aggregate calculation based on the input values. SUBTOTAL, despite its name, is capable of a wide range of calculations, including SUM, AVERAGE, COUNT, MAX, MIN, and others listed in the table below.

Function	Include hidden	Ignore hidden	Know more
AVERAGE	1	101	Practice AVERAGE
COUNT	2	102	Practice COUNT
COUNTA	3	103	Practice COUNTA
MAX	4	104	Practice MAX
MIN	5	105	Practice MIN
PRODUCT	6	106	Practice PRODUCT
STDEV	7	107	Practice STDEV
STDEVP	8	108	Practice STDEVP
SUM	9	109	Practice SUM
VAR	10	110	Practice VAR
VARP	11	111	Practice VARP

The SUBTOTAL function can include or exclude values from hidden rows, making it particularly useful in Excel Tables and filtered data. SUBTOTAL by default ignores values in rows hidden by a filter. SUBTOTAL returns an aggregate result from a set of data, regardless of the calculation used.

To avoid double-counting, the SUBTOTAL function automatically ignores other SUBTOTAL formulas in references.

Examples

Below are  examples of SUBTOTAL configured to SUM, COUNT, and AVERAGE the values in a range. Notice the only difference is the value used for the function_num argument:

=SUBTOTAL(109,range) // SUM
=SUBTOTAL(103,range) // COUNT
=SUBTOTAL(101,range) // AVERAGE

In the worksheet shown above, the formulas in C4 and F4 are:

=SUBTOTAL(3,B7:B19) // count visible
=SUBTOTAL(9,F7:F19) // sum visible
=SUBTOTAL(3,B7:B19) // count visible
=SUBTOTAL(9,F7:F19) // sum visible

Note: SUBTOTAL always ignores values in cells that are hidden with a filter. Values in rows that have been “filtered out” are never included, regardless of function_num.

Practice subtotal function below:

The formula can be entered in the highlighted cell. If the formula entered is correct, it shows good job. If the formula is entered incorrectly, the result will be invalid. Please keep in mind that you can only enter formula in the highlighted cells.

Find the sum of total sales using subtotal function:

Hurray! You know now how to use subtotal function. Practice other functions by checking our articles below.