SUBTOTAL Function in Excel


Home >> How to Use Excel Tutorial >> Excel Formulas List >> SUBTOTAL Function in Excel



In this tutorial, you will learn about how to use SUBTOTAL formula in excel. SUBTOTAL function in excel is useful when using filter or table function in this spreadsheet software for the number data and you need a tool to do calculation process on some parts of the cell range which are filtered or in a table.


Why We Need to Learn About SUBTOTAL Formula in Excel?


In using spreadsheet tool, often we use filter or table function to group data based on the kinds of variables that we have. In the grouping process, usually, there is number data on each of data entry and we often need to do special calculation on a particular group which is the result from filter or table function to get the result needed in our data analysis or processing.

As an example, let’s say there is a data collection about product sales of a company that is saved in this spreadsheet software. Usually, there are many variables in this kind of data such as kinds of product, sales region, price, units sold, discount, etc. In the data analysis, probably we want to see how many total sales of a product kind in a particular region or sales average of all products in a region with a specific discount amount to see whether a marketing campaign with a specific discount amount is successful or not. For that, a process is needed to do various calculations to the group of data so the data processing needed can be done optimally.

If using formulas like SUM or AVERAGE directly, then the objective will be very hard to achieve because those formulas will calculate all numbers in the cell range without seeing whether a data row is hidden by filter or table because it is not included in the data grouping which wants to be seen at that time.

For this condition, we can use SUBTOTAL function in excel. SUBTOTAL formula in excel is very useful to be understood if there is a need to calculate a group of data from a whole-cell range where we often use filter or table function to do the grouping process.


What is SUBTOTAL Function in Excel?


SUBTOTAL function in excel is a formula to do the calculation process to a group of numeral data in a cell range. SUBTOTAL formula in excel is usually used if you utilize filter or table function in this spreadsheet software.

General description on the inputs in this formula is as follows:


=SUBTOTAL(function_num, ref1, …)


Note:
  • function = the kind of calculation that you want
  • ref1, … = all the cell ranges to be included in the calculation



How to Use SUBTOTAL Formula in Excel?


The following will explain how to use SUBTOTAL function in excel. SUBTOTAL formula in excel needs two inputs which are formula code to be used by SUBTOTAL function in excel and cell range that will be calculated based on the code input. The detail of formulas that can be used in SUBTOTAL formula in excel and the related code are as follows:

FormulaCalculate Cells Which Are Hidden ManuallyIgnore Cells Which Are Hidden Manually
AVERAGE1101
COUNT2102
COUNTA3103
MAX4104
MIN5105
PRODUCT6106
STDEV7107
STDEVP8108
SUM9109
VAR10110
VARP11111


Use codes above according to your needs in calculating your data group!


Using SUBTOTAL Function in Excel

  1. Type equal sign ( = ) in the cell where you want to put the result in

    SUBTOTAL Function in Excel - Screenshot of Step 1

  2. Type SUBTOTAL (can be with large and small letters) and open bracket sign after =

    SUBTOTAL Function in Excel - Screenshot of Step 2

  3. Type formula code or cell coordinate where the code that you want to use is in (for the correct code for your calculation needs, see the reference on the above table), after open bracket sign, then type comma sign ( , )

    SUBTOTAL Function in Excel - Screenshot of Step 3

  4. Drag cell cursor from the most top left to the most bottom right for the cell range that is wanted to be calculated. You can also type comma sign ( , ) and add cell range in another place until all of them are in the formula

    SUBTOTAL Function in Excel - Screenshot of Step 4

  5. Type close bracket sign

    SUBTOTAL Function in Excel - Screenshot of Step 5

  6. Press Enter
  7. The process is done!

    SUBTOTAL Function in Excel - Screenshot of Step 7



Exercise


After you have learned the usage steps of SUBTOTAL formula in excel from the tutorial above, you can practice your understanding of them by doing this exercise!

Download the file from the following link and answer the questions. Please download the answer if you have done answering all the questions and sure about the results!

Link to download SUBTOTAL function in excel exercise file:
Download here

Questions

Use TEXT formula to answer! Look at the Additional Note part if you are confused about the numeral data format input!
  1. What is the maximum value in column C if column A consists of letter A and column B consists of letter A or B?
  2. What is the average of column C value if column A consists of letter A and column B consists of letter A or B?
  3. What is the total of column C if column A consists of letter A and column B consists of letter A or B?

Link to download answer:
Download here


Additional Notes

  • Code input less than 100 will only include cells that are hidden manually. Hidden cells from filter result are still ignored in the calculation
  • SUBTOTAL formula in excel ignores other SUBTOTAL results in cell range
  • This formula is designed to work on vertically arranged data. If the data is arranged horizontally, then hidden cells will always be included in the calculation process




Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert