The Excel SUBTOTAL function calculates subtotals and grand totals in a list for a column. Hence, The SUBTOTAL function can perform various calculations such as COUNT, SUM, AVERAGE, etc. Therefore, it helps in analysing the data provided.
What distinguishes the SUBTOTAL function from the regular TOTAL function is that the SUBTOTAL function always ignores values in cells that are hidden with a filter. However, you can only include the hidden items within the calculations if you apply them manually.
Syntax:
|
1 |
=SUBTOTAL (function_num, ref1, [ref2], ...) |
The excel SUBTOTAL performs 11 functions to analyse the data. In other words, these functions specify which function to use in calculating subtotals within a list. However, each function comes with two options, to either include manually hidden data or to exclude them. For more details, the below table shows all SUBTOTAL functions with the two available options:
| FUNCTION | INCLUDE HIDDEN | EXCLUDE HIDDEN |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Example 1
The following example calculates the SUM of all numbers that are in the range A2 through A10 when the filter is applied:
|
1 |
=SUBTOTAL(9,A2:A10) |
Example 2
The following example calculates the AVERAGE of all numbers that are in the range A2 through A10 when the filter is applied:
|
1 |
=SUBTOTAL(1,A2:A10) |
Example 3
The following example calculates the PRODUCT of all numbers that are in the range A2 through A10 when the filter is applied. PRODUCT Function multiply all numbers together in a range.
|
1 |
=SUBTOTAL(6,A2:A10) |
More details in Microsoft Excel Subtotal Function