Go Back

Excel SUBTOTAL Function

The Excel SUBTOTAL function is used to find out an aggregate for some given values. SUBTOTAL function can be used to return different results in the form of SUM, COUNT, MAX, AVERAGE, and other results. In short, SUBTOTAL function is used to get a subtotal from a list of values.

How to apply SUBTOTAL Function in Excel?

To perform various subtotal tasks we apply SUBTOTAL function in MS Excel. There are several tasks which SUBTOTAL can do and a comprehensive list of tasks with their function number is given below. To apply a SUBTOTAL function we simply have to insert a formula in a tab and SUBTOTAL function would return the result. See the example below for step by step guide on how to apply SUBTOTAL function.

Formula or Syntax:

=SUBTOTAL (function_num, ref1, [ref2], ...)


  • function_num: It is the number pre-specified to tell SUBTOTAL what task to perform. There is a comprehensive list given below.
  • ref1 – A range to which the SUBTOTAL function to apply.
  • ref2 – (optional) It is an optional function for a named reference or range.

List of SUBTOTAL Functions:

Every SUBTOTAL function can be performed in two ways. It can either include the hidden values or exclude them. Below is a list of functions with their separate function numbers as well:


Here we will apply a very straightforward formula in a cell and learn how to apply SUBTOTAL function step by step,

  • In the example, we have a list of items from C4:C9 and their prices from D4:D9.

Figure 1. List of data

Now we want to obtain a SUBTOTAL of this list and calculate the number of fruits in the list and get the result in cell G5. We will go to cell G5 and insert the following formula.


Figure 2. Applying SUBTOTAL formula

We have got the SUBTOTAL result in our cell G5.

Figure 3. The result of SUBTOTAL function


  • When we apply function_num between 1-11, SUBTOTAL always includes the hidden values.
  • When we apply function_num between 101-111, SUBTOTAL always excludes the hidden values.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Use the Subtotal function: o Insert a blank row between rows 10 and 11 (acquisition amounts 2,100 and 900). o In the Net Book Value column add a subtotal (sum) for the first 6 rows using the subtotal function. o Add a top and bottom border for the subtotal at K11 that you just created. o Add a second subtotal function atK19 for the remaining seven rows in the column. o In K20, use the subtotal function for all 13 rows ignoring the two subtotals just created.
Solved by V. J. in 18 mins
subtotal not working
Solved by T. C. in 18 mins
When using the function 'subtotal 9' the data is filtered and once I change the filter the subtotal changes too. I want to keep the original subtotal as I need to get a few different subtotals for my project.
Solved by V. L. in 26 mins

Leave a Comment


For example, in column A we have A1=1; A7=5; A12=8;A21=6 and so on.
How to formulate to fill the following empty cells from A2 to A7 with 1, from A8 to A12 with 5 and so on?
Thank you

Comment awaiting moderation