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 wholecell 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:
Formula  Calculate Cells Which Are Hidden Manually  Ignore Cells Which Are Hidden Manually 

AVERAGE  1  101 
COUNT  2  102 
COUNTA  3  103 
MAX  4  104 
MIN  5  105 
PRODUCT  6  106 
STDEV  7  107 
STDEVP  8  108 
SUM  9  109 
VAR  10  110 
VARP  11  111 
Use codes above according to your needs in calculating your data group!
Using SUBTOTAL Function in Excel

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

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

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 ( , )

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

Type close bracket sign
 Press Enter

The process is done!
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! What is the maximum value in column C if column A consists of letter A and column B consists of letter A or B?
 What is the average of column C value if column A consists of letter A and column B consists of letter A or B?
 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