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], ...)
Arguments:
- 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:
Example:
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.
=SUBTOTAL(3,C4:C9)
Figure 2. Applying SUBTOTAL formula
We have got the SUBTOTAL result in our cell G5.
Figure 3. The result of SUBTOTAL function
Notes:
- 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.
Leave a Comment