Did You Know :: Excluding Hidden Cells from SUM (and other) Formulas – SUBTOTAL

Posted: June 23, 2013 by MaxFraudy in Excel
Tags: , , ,

UntitledProblem Statement: I have grade-wise student numbers in column C as given below. I have written a formula in cell [A2] that sums up the total number of students in each grade.

Data

Data

Now I want to put a filter on grade, i.e. column A, and see the total number of students in that grade in all sections. If I simply use Autofilter, it will not give the desired result.

Solution: The solution to this problem is a built-in function in Excel called SUBTOTAL. This function takes two arguments, the first one is function number and the next is range to use. The first argument tells Excel which calculation to perform (see table at the bottom for more info). In cell [B2], I write SUBTOTAL formula. If I apply filter now, the result is what one would expect as shown below:

Subtotal vs Sum

Subtotal vs Sum

You will notice that I have used two values as the first argument for the function – 9 & 109, both of them giving the same result. This is because we have not hidden any rows. If you hide any rows, the results in [B2] and [C2] will be different. While arguments 1 to 11 exclude filtered rows but include hidden rows, arguments 101 to 111 excludes filtered as well as hidden rows. Try it for yourself!

Argument List for SUBTOTAL                                                    

Includes Hidden Values Excludes Hidden Values Function Performed
1 101 Average
2 102 Count
3 103 Counta
4 104 Max
5 105 Min
6 106 Product
7 107 StDev
8 108 StDevP
9 109 Sum
10 110 Var
11 111 VarP
Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s