Using the Excel SUBTOTAL Function


The Excel SUBTOTAL function returns a subtotal in a list or database. SUBTOTAL can return a SUM, AVERAGE, COUNT, PRODUCT, and others. The function can also include or exclude values in hidden rows.


=SUBTOTAL (function_num, ref1, [ref2], ...)

Syntax Breakdown

Function_num is a required argument. The numbers 1-11 or 101-111 are used to specify the function to be used for the subtotal. Numbers 1-11 are used to include manually hidden rows. Numbers 101-111 will exclude manually hidden rows. Cells that are filtered out will always be excluded.

The following table lists the numbers and their corresponding function.

Excel SUBTOTAL Function_num Values

Ref1 is a required argument and is the first range or reference to which you want to find the subtotal.

Ref2 and any subsequent ranges or references are optional. The function can handle up to 255 references.

Usage Notes

The SUBTOTAL function is used to get a subtotal from a list or database. SUBTOTAL is able to use a variety of functions (see complete list above). The function, by default, will exclude manually hidden rows.The function will always ignore values that have been filtered out, regardless of the reference_num argument.

The function returns the #VALUE! error if any of the references are 3D references.

SUBTOTAL is designed to handle data in columns (vertical ranges) and not rows (horizontal ranges). For example, if you had the function =SUBTOTAL(101, A1:G1) and hide a column it will not affect the subtotal. However, if you hide a row that is part of a vertical range in the function the subtotal will be affected.

Manually Hidden Values
SUBTOTAL allows you to specify if it should pay attention to manually hidden rows. As you can see in the above table, when reference_num is 1-11, the function includes manually hidden value. When reference_num is 101-111, the function ignores manually hidden values.

Nested Subtotals
Nested subtotals, or other subtotals within ref1, ref2, etc., will be ignored so as to avoid double counting.

Viewing Hidden Rows
To view hidden rows navigate to the Cells group on the Home tab. Click the Format drop down option and select Hide & Unhide. There you will be able to hide and unhide rows and columns.