Among the functions that are featured in Microsoft Excel, the SUBTOTAL function is probably the most versatile. This function can be used for different purposes unlike the other Excel functions which can only do one thing.

The SUBTOTAL function in Microsoft Excel is employed to get the subtotal of a given array of cells. The function’s formula requires two things, the type of subtotal and an array of values. Then the SUBTOTAL function will return the result which is the subtotal for that range of values.

Unlike other Excel functions like COUNT, AVERAGE, SUM and many more, which can only be used for one purpose, the SUBTOTAL function is flexible. It has the ability to count, average and sum up a group of values.

The formula for the SUBTOTAL function is:

=SUBTOTAL (function_num, Ref1, [Ref2])

  • In this formula, the function_num argument is a number that indicates the type of calculation (ex. average, product, sum and many more).
  • The Ref1, [Ref2] argument is the cell references which contain the data in which you want to calculate for.

The following are the possible values of the function_num argument. If you plug in the value of the function_num argument in the formula, the SUBTOTAL function will perform the type of calculation which corresponds to the value of the argument.

  • 1 = AVERAGE
  • 2= COUNT
  • 3= COUNTA
  • 4= MAX
  • 5= MIN
  • 6= PRODUCT
  • 7= STDEV
  • 8= STDEVP
  • 9= SUM
  • 10= VAR
  • 11= VARP

To show you how the SUBTOTAL function is used, here is an example.
For example, the following cells contain these values:

  • A1 – 45, 000
  • A2 – 36, 000
  • A3 – 55, 000
  • A4 – 39, 000
  • A5 – 47, 000
  • A6 – 61, 000
  • A7 – 41, 000
  • A8 – 52, 000
  • A9 – 38, 000

For instance, you want to compute for the sum and average of the values. You can type in the SUBTOTAL function formula in any cell where you want the result to be displayed. Enter this formula in cell B1 to calculate the sum of the values, =SUBTOTAL (9, A1:A9). Then the function will return the result which is 414, 000. Since the value of the function_num argument is “9”, the SUBTOTAL function will sum up the values.

Then to compute for the average of the values, type in this formula in cell B2, =SUBTOTAL (1, A1:A9). The function will return the result which is 46, 000. Since the value of the function_num is 1, the SUBTOTAL function will get the average of the values.
5 Reasons Why to Use the SUBTOTAL Function

  • SUBTOTAL function can be used to sum up filtered values
  • It can be used to ignore hidden values.

Oftentimes, you may hide insignificant values in your spreadsheet. With the special type codes of the SUBTOTAL function, those values are neglected.

  • SUBTOTAL function can be used to summarize values dynamically.
  • Subtotals that exist in a range of values are neglected.

This is one of the most important features of the SUBTOTAL function. If subtotals already exist in a range of values, they are neglected so that the user can avoid double counting.

  • You can get subtotals automatically by using Excel Data Tools

Although the SUBTOTAL formula is quite easy to use, you may still find it difficult to use it when you are working with a huge tabular data. Luckily, in the Data menu of Excel, you can select an option known as the “Subtotals” for an automatic process of calculations.