What is the use of SUBTOTAL Function in Excel?

Data Science, AI and ML
,
1

The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.

  • Purpose: Get a subtotal in a list or database
  • Return value: A number representing a specific kind of subtotal

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.

Example:

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