SUBTOTAL Function in Excel - Compute Expert

# SUBTOTAL Function in Excel

Home >> Excel Tutorials from Compute Expert >> 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 2. Type SUBTOTAL (can be with large and small letters) and open bracket sign after = 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 ( , ) 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 5. Type close bracket sign 6. Press Enter
7. 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!

### 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?

• 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

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