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
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
The Subtotal() shown above with a simple SUM function. You can apply the others function number as mentioned in Subtotal number chart above.