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.