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_numCalculation performedDescription
1AVERAGEReturns the average (mean)
2COUNTCounts number of values or cells which contain numbers
3COUNTACounts number of values or cells which are not empty
4MAXReturns the largest value
5MINReturns the smallest value
6PRODUCTMultiplies all the numbers
7STDEV.SEstimates standard deviation based on a sample
8STDEV.PCalculates standard deviation based on a population
9SUMAdds 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

No comments :

Post a Comment

Thanks for your feedback. The elves will post it shortly.