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

• Reference: Office Support (2019). SUBTOTAL function. Retrieved from https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939