# SUBTOTAL

## 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 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP

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. 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.