The SUBTOTAL Function Can Do That?

One often overlooked function in Excel is SUBTOTAL().  This function can not only add a series of number together, but it can perform any of the typical statistical aggregates.  Suppose you have a simple table of test scores by individual student as shown in the following figure:

You are probably familiar with the AVERAGE(), SUM(), MIN(), and MAX() functions, but did you know that you could do everything these functions do with a single function and more?  First let me list the possible values for the first argument of the SUBTOTAL() function along with the corresponding individual functions.

 SUBTOTAL Function Regular Function =SUBTOTAL(1,ref1,[ref2],…) =AVERAGE(number1,[number2],…) =SUBTOTAL(2,ref1,[ref2],…) =COUNT(number1,[number2],…) =SUBTOTAL(3,ref1,[ref2],…) =COUNTA(number1,[number2],…) =SUBTOTAL(4,ref1,[ref2],…) =MAX(number1,[number2],…) =SUBTOTAL(5,ref1,[ref2],…) =MIN(number1,[number2],…) =SUBTOTAL(6,ref1,[ref2],…) =PRODUCT(number1,[number2],…) =SUBTOTAL(7,ref1,[ref2],…) =STDEV(number1,[number2],…) =SUBTOTAL(8,ref1,[ref2],…) =STDEVP(number1,[number2],…) =SUBTOTAL(9,ref1,[ref2],…) =SUM(number1,[number2],…) =SUBTOTAL(9,ref1,[ref2],…) =VAR(number1,[number2],…) =SUBTOTAL(9,ref1,[ref2],…) =VARP(number1,[number2],…)

(Of course you can include references to individual cells or cell ranges in these formulas.  Cell ranges are defined by the cell address of opposite corners of the grid separated by a colon as in an individual column: B2:B5 or a row: B2:D2 or an area made of rows and columns (B2:D5).)

By default, the above function values listed in the above table include all rows and or columns in it calculation, even hidden rows or columns.  But what if you wanted to just perform the function on rows and columns that were visible?  Simple.  Just add 100 to the first argument.  In other words, to calculate the average of only the rows in the table shown above, use =SUBTOTAL(101,B2:B5).  This ability to ignore hidden rows and columns is not a an ability of the regular functions such as =AVERAGE() which always includes all rows and column whether they are hidden or not.  (See the figure below in which row 3 has been hidden.)

Another interesting feature of the SUBTOTAL() function is that it ignores cells that contain formulas in the included range.  The following figure shows the grades for two small classes.  (Of course you could imagine a larger number of classes with more students in each.)  As you can see in the following figure, we can easily use SUBTOTAL to calculate sums, averages, counts, variances, etc across the entire column range while correctly skipping formula, label and blank columns which the regular formulas do not skip resulting in incorrect results shown in the cells in the bottom left of the figure.

Hope this carries you need for ‘cool’ Excel technology while I talk about some SharePoint issues over the next few weeks.