Subtotal Formula in Microsoft Excel

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.

 

Add a Comment

Your email address will not be published. Required fields are marked *