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:

ValueExplanation
(includes hidden values)
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV
8STDEVP
9SUM
10VAR
11VARP

 

ValueExplanation
(ignores hidden values)
101AVERAGE
102COUNT
103COUNTA
104MAX
105MIN
106PRODUCT
107STDEV
108STDEVP
109SUM
110VAR
111VARP

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.

 

Leave a Reply

Close Menu
×

Cart