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.
• 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 :
There are two steps to use the SUBTOTAL function in excel. These are:
1. Formatting and sorting of the provided data.