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

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.

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

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

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

### MIN

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

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

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