## Wednesday, 17 July 2019

### MS Excel Sub-total functions

I use autofilter in Excel as a matter of course, and with that, I use the sub-total function, so that when I filter the list, I get the totals for the filtered list.

The best thing is that, when you use sub-totals, you sub-total a range of things other than just a straight sum, including counts of text fields, averages, maximums and minimums. I thought I had written about this before, but - following a blog search - apparently, I have not!

The sub-total function is very useful. The following table shows what each function number delivers (Office Support, 2019):

 function_num Calculation performed Description 1 AVERAGE Returns the average (mean) 2 COUNT Counts number of values or cells which contain numbers 3 COUNTA Counts number of values or cells which are not empty 4 MAX Returns the largest value 5 MIN Returns the smallest value 6 PRODUCT Multiplies all the numbers 7 STDEV.S Estimates standard deviation based on a sample 8 STDEV.P Calculates standard deviation based on a population 9 SUM Adds all the values or cells in a range

The easiest way I have found to use sub-totals is to add a sub-total row above the data at the top of a spreadsheet, filter the spreadsheet, select the cell we want to see the sub-total in, then click the sum button. For example, in the image accompanying this post, I originally got:

=SUBTOTAL(9,G7:G500)

Then we can edit function number the resulting sub-total for the function we want. To get an average, I changed the 9 to a 1:

=SUBTOTAL(1,G7:G500)

And then we get an average of the range.

I use sub-totals all the time. Hopefully they will prove useful for all of us!

Sam