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 such as SUM, COUNT, , COUNTA, PRODUCT, AVERAGE, MAX, etc.

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

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