SUBTOTAL Function in Excel | Itech Education

The SUBTOTAL function in Excel is very helpful to analyze data of provided range. The SUBTOTAL Function allow to make filter and then perform various other Excel functions.

Use the SUBTOTAL function to exclude filtered or hidden rows when calculating a total on the worksheet.

Syntax:

• function_num: a number that specifies the type of calculation to be performed. This can be any of the following values:
1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX
5 - MIN
6 - PRODUCT
7 - STDEV
8 - STDEVP
9 - SUM
10 - VAR
11 - VARP

• ref1: the first range of cells to include in the calculation.

• [ref2]: (optional) additional ranges of cells to include in the calculation.

Syntax :

SUBTOTAL = (method, range1, [range2 …range_n])

How to use the SUBTOTAL Function in Excel?

There are two steps to use the SUBTOTAL function in excel. These are:

1. Formatting and sorting of the provided data.

2. Applying SUBTOTAL to the table.

These are Functions which you can use using SUBTOTAL:

• 1 or 101: AVERAGE

• 2 or 102: COUNT

• 3 or 103: COUNTA

• 4 or 104: MAX

• 5 or 105: MIN

• 6 or 106: PRODUCT

• 7 or 107: STDEV

• 8 or 108: STDEVP

• 9 or 109: SUM

• 10 or 110: VAR

• 11 or 111: VARP