If you have ever added a total to a table you might have noticed that it doesn’t use a SUM function, but a strange thing called SUBTOTAL (And if you never have added a total to a table, then you really should! You can read about the greatness of tables here).
So, why does the table use SUBTOTAL??
SUBTOTAL is a function that had a couple of very neat features. When you filter the table, the SUBTOTAL function will show the total of only the visible rows, unlike SUM which will show the ‘grand total’ of the whole table, visible and hidden rows. Also, SUBTOTAL will not include itself in a total. So, if you have a column of numbers, which includes subtotals, and then a SUBTOTAL function at the end, it will give you a total of the figures, and not a double count.
SUBTOTAL(function_num,ref1,[ref2],…)
SUBTOTAL actually does a lot more than just the sum. It can do average, min, max, count and more.
That is why you have to give at least two inputs: which function you want it to preform, and the range of cells to do that function to. And to confuse things even more, each of the functions come in two types.
1 or 101 for average, 2 or 102 for count, 9 or 109 for sum etc.
The difference between the 1-11 numbers and the 100 – 111 numbers are how manually hidden rows are dealt with. 1-11 with include rows that have been hidden. 100-111 will ignore the values in hidden rows. Both will ignore rows that have been filtered out.
As you start to type the SUBTOTAL, Excel will pop up an input box, so you can choose which function number you want. You don’t need to remember 1 for average, 2 for count, etc. And, if you are using a table, you don’t even need to type the function at all! Just choose from a drop down list, and Excel creates the function for you.