Free Online Tutorials on Spreadsheets

SUBTOTAL function in Excel

The SUBTOTAL function uses to find subtotal of a list or database. The unique feature of the function is that it can ignores or include hidden rows. SUBTOTAL function can either exclude or include hidden rows and performs AVERAGE, COUNT, COUNTA, MAX, and others (see table provided below).

Syntax

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

The SUBTOTAL function’s syntax has the following arguments:

Function_num – Required. The number specifies which function to be used for subtotal.

Function_num 
(includes hidden values)
Function_num 
(ignores hidden values)
Function
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

ref1 – Required. The named range or reference for which subtotal is required.

[ref2] – Optional. The named range or reference for which subtotal is required.

Example

Result.

After un-filter of data.

Related Topics

Subtotal invoices by age (date) in Excel

Subtotal by invoice number in Excel




Copyright © 2016 - 2019 Explainry.com | All Rights Reserved