Excel version in which SUBTOTAL function can work:
Excel SUBTOTAL formula can work in all of your latest version of Microsoft office.
About SUBTOTAL function in excel:
Subtotal Formula helps to find out subtotal of the numbers in a column from the database. It is a built in function in Excel, that can be categorize as Math or Trig function. It can be used also as a worksheet function. This function can be clubbed with other formula also.
Syntax for SUBTOTAL Function:
=SUBTOTAL(function_number,ref1,[ref2],…..)
Parameters
function_number: The type (method) of creating SUBTOTAL function, you should be careful while selecting the method. Method can be raging from 1 to 11, includes hidden values and values ranging from 101-111, that ignores hidden values in the calculations. Various methods are shown in the table:
| Value | Explanation |
| (includes hidden values) | |
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV |
| 8 | STDEVP |
| 9 | SUM |
| 10 | VAR |
| 11 | VARP |
| Value | Explanation |
| (ignores hidden values) | |
| 101 | AVERAGE |
| 102 | COUNT |
| 103 | COUNTA |
| 104 | MAX |
| 105 | MIN |
| 106 | PRODUCT |
| 107 | STDEV |
| 108 | STDEVP |
| 109 | SUM |
| 110 | VAR |
| 111 | VARP |
Range: Ranges of cells that you want to subtotal.
Using Excel SUBTOTAL function:
Subtotal function helps you to totalling when the data is filtered. This function totals only the filtered numbers.
Example Subtotal function:
In Picture 1, Data is showing without filtering this includes all the numbers.
In Picture 2, After inserting filter total automatically changes by taking only filtered numbers.
