Subtotal Function

Subtotal function is a versatile function that can answer a lot of your needs all by itself. Besides it has some cool tricks in its sleeve.

Syntax of subtotal function:

=SUBTOTAL(Function Number;Range)

Subtotal function can be used for several purposes.  Here is a list of things that this function does:

But this function does these with a catch. If you add 100 to function numbers, subtotal function will ignore hidden/filtered values. This will allow you to make dynamic sums, averages, etc.

For example, you need to use 109, instead of 9 as function number. And result will be like this:

Another good thing about subtotal function is, it ignores any subtotals in its calculation range. This mean by using subtotal, you will avoid duplicated sum’s therefore double values.

You can also achieve the kind of subtotals shown in the picture above, simply by selecting your data and clicking on the subtotals button located on the data ribbon.

I cannot tell you to drop sum, average, etc. functions from now on and continue with subtotal function. But it certainly has some advantages that will come in handy in certain situations. So it is a “good to know” function for your Excel toolkit.