Subtotal function in Excel

Subtotal() is basically used to get instant summary of a filtered data table. It is used with the support of AutoFilter tool or can be used alone. If you use AutoFilter for Subtotal then all hidden rows become visible. But if you do not use AutoFilter then hidden rows becomes hidden.

Subtotal Function:

The Subtotal function structure is:

=SUBTOTAL(function_number,ref1)

Function Details:

There are 2 main parts of this function. These are:

function_number: Subtotal function can calculate many things like, SUM, COUNT, AVERAGE etc. Each of these function are stored with a number in Subtotal(). What you need to do is mention the Number first from below list:

Image 1: SUBTOTAL() number chart

ref1: This is nothing but a Range. You can Select a single cell or few cells selected randomly, or a range of cells.


Example:

Based on below data table assume that, you need to know the Salary of Mr. A in D4 cell.

Image 2: Sample data table for Subtotal

In D4 cell use the below function:

=SUBTOTAL(9,D2:D12)

As I have entered 9 as function_number, based on the Subtotal() number chart, you will see that, the result will be calculated including all hidden rows. In the above sample data there are 2 different salary found for Mr. A. ($5215 + $3362) = $8577 will be the answer as there is no hidden rows for Mr. A.

 Image 3: Subtotal for Mr. A returns 8577

If the top row was hidden, whose amount is $5215, then the result will be same ($5215+$3362) = $8577 and the surprising thing is that, the hidden row will automatically unhide and displayed in the table while using AutoFilter.

Note: 

If you apply Subtotal() and use AutoFilter, all the hidden rows will become shown and calculate as including Hidden values.

Now what will happen If I use 109 as function_number for the data table? Before using it, Calculate a SUM of all the entered salary. You will get: $57264.

Image 4: Total Value of Salary

For experiment, hide the row 6, 10, 11 where Salary is $3362+$9963+$3214 = $16539. Lets see what is the result showing now:

Image 5: Same result

Now, Use the function as previously used, but use 109 instead of 9 in D14 cell as function_number, which will calculate without hidden rows value:

=SUBTOTAL(109,D2:D12)

See, the result:

 Image 6: Different result

Without applying the AutoFilter, the SUBTOTAL(109,D2:D12) showing $40725 where $16539 has hidden. Now if you Click on AutoFilter DropDown list and choose anything then automatically all rows will be visible (unhidden) and calculate the total number $57264.

The Subtotal() shown above with a simple SUM function. You can apply the others function number as mentioned in Subtotal number chart above.