SUBTOTAL

Return the dynamic operations to the summary value in Excel
Use several types of summary functions in table

Rational

Returns multiple types of calculation in a data range

Description

The Excel SUBTOTAL function returns value using any of 11 calculations depending upon the user input. It is a flexible function that provides the ability to summarize a list or data in multiple ways by determining the actual function.

Syntax

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

Parameters

function_num – To determine which type of calculation
ref1 – The first range of cells or array to calculate
ref2 [optional] – The second range of cells or array to calculate

Return Value

Summarize value of the list

Key Notes

  • The SUBTOTAL function is a versatile function with a misleading name as it does many different Excel operations apart from SUM function. Like AVERAGE, MAX, MIN, COUNTA, and other functions mentioned below.
  • The SUBTOTAL function ignores any cells of reference data that already contain a formula with SUBTOTAL in them.
  • Up to 255 ranges of numbers are allowed.

Following are different Excel functions that you can use by using SUBTOTAL functions alone:

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The most interesting fact of the SUBTOTAL function is that it calculates data ignoring hidden row.

Calculate summary data even if rows are hidden

Let’s take an example where you count the number of sales figure in our data sets that returns 12, using the Excel COUNT function.

This function will always return 12 as the number of data irrespective the cell is hidden or visible after the user applies a filter in the table. But if we use the SUBTOTAL function the count will vary depending on how many rows are visible.

SUBTOTAL function returns the dynamic value if filter is active in Excel
The SUBTOTAL function returns the dynamic value if the filter is active.

In case the first argument is more than 100, the SUBTOTAL function behaves a bit differently.

Unlike previously where we required a filter table to ignores the hidden row if we opt for arguments more than 100 then it does not include data that were hidden manually but exclude data in rows that were hidden as a result of filtering.

Return summary value even if rows are hidden manually in Excel
Return summary value even if rows are hidden manually

Keep Reading Similar Functions:

Return the smallest value in Excel

MIN

The MIN function returns the smallest numerical with the lowest value as per given reference of cell range.

Read More »
Counts cells with only Numbers in Excel

COUNT

The COUNT function count of numbers within the reference list of arguments ignoring text & blank cells, =COUNT(A1:A10) will return 5 (only numeric).

Read More »
To add multiple numbers in Excel

SUM

The SUM function returns a number after adding multiple numeric arguments together =SUM(5,10,15) the Excel add numbers together and returns 30.

Read More »
Return the largest value in Excel

MAX

The MAX function returns the highest value as per cell range, if A1:A5 contains a list of numbers (2,8,10,5,6), the formula can be =MAX(A1:A5), returns 10.

Read More »
Counts cells that are not blank in Excel

COUNTA

The COUNTA function count of numbers and text within the reference list of arguments ignoring blank cells, =COUNTA(A1:A10) will return 7 (disregard blank).

Read More »
Find the average of numbers in Excel

AVERAGE

The AVERAGE function returns an average of numbers positioned in one or multiple ranges, =AVERAGE(5,15,30) add numbers and divide by 3, and returns 16.67.

Read More »