Once we have applied filter to our data using the excel’s “AutoFilter” option and we want to sum only the non-hidden range, then we can’t use the simple “sum” function as it adds both the hidden and visible cells. In that case we can use the excel’s subtotal function as it only sums the visible range.
The syntax of the Subtotal function is:
SUBTOTAL(function_num,range_reference1, range_reference2….)
We can perform 11 different functions in conjunction with subtotal.
Function_Num - Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
Suppose you have a list of 4 different products - A, B, C and D along with their quantity sold.
And you wish to add up only the quantity sold of product A. Then if you apply filter and sum the range using “Sum” function then it will give the total for all the products
Whereas if you use subtotal function you will only get it for product A as shown below:
Popularity: 11%