SUBTOTAL Formula in Excel (Table of Contents)
SUBTOTAL Formula in Excel
Sometimes we need to find the subtotal of a category from a large set of data that has multiple categories. In that kind of situation subtotal function will help us to find the total category wise. Not only subtotal we can calculate the average, count and max and many more.
Definition
The function that returns subtotal from a list or database can be defined as Subtotal function. Subtotal is a special function compared to other excel functions because other excel functions will perform only one specific operation, but the specialty of subtotal function is, it not only performs subtotal calculation, but also multiple arithmetics and logical operations depend on the function number.
Syntax of SUBTOTAL Formula
Arguments:
Function num: It refers to the type of mathematical operation we are going to perform on a specified range of data.
Ref1, Ref 2: It refers to the range of cells.
There are a multiple number of function numbers available but no problem we no need to remember all these function numbers because, while using the SUBTOTAL formula excel will automatically show you the list of function numbers available as below.
You must be wondering why there are two function number for the same function.
- 1 – 11 function numbers should use when we want to ignore the filtered-out cells and consider the manually hidden cells.
- 101 – 111 function numbers should use when we want to ignore all the hidden cells which are filtered out and manually hidden cells.
We will see a few examples to understand how subtotal function will work for different function numbers.
How to Use SUBTOTAL Formula in Excel?
SUBTOTAL Formula in Excel is very simple and easy. Let’s understand how to use the SUBTOTAL Formula in Excel with some examples.
Example #1
Consider a small table which has data from different categories as below.
If you observe the above table, we have Product category, Color category, and Quantity.
Apply the SUBTOTAL Formula as below with function number 9.
After applying this formula the result is as shown below.
Apply the SUBTOTAL Formula as below with function number 109.
After applying SUBTOTAL Formula the result is shown below.
In the Formula, we have used both the function number 9 and 109 for performing SUM in two different columns. C2:C9 is the range of data that we are performing calculations.
Now, the total sum is 385 for both the formulas. Hide a few rows and observe the results for both the formulas.
After applying the SUBTOTAL Formula result is shown below.
Rows from 50 to 60 are hidden, hence the results of the function number 109 has changed to 255 because it does not consider the manually hidden data whereas function number 9 total remains same which means it will consider the data even though we hide the data manually.
Apply filter to the data and filter only one color, here we are selected the Black Color.
After applying the SUBTOTAL Formula result is as shown below.
Apply the SUBTOTAL Formula as below with Function Number 109.
After applying the SUBTOTAL Formula result is as shown below.
If we observe the above screenshot both the function numbers did not consider the quantity of filtered out data. After you filter out the data and hide rows then both formulas will give the same results that both formulas will not consider non-visible data.
After applying SUBTOTAL Formula the result is as shown below.
Apply the SUBTOTAL Formula as below with Function Number 109.
After applying this formula the result is as shown below.
Example #2
Up to now, we have seen SUM operation, now we will perform the AVERAGE operation with the same data range. 1 and 101 are the function numbers to perform average. Go through the above table for the function numbers details.
After applying the SUBTOTAL Formula result is as shown below.
For the next cell, apply the SUBTOTAL Formula again with function number 109.
After applying formula the result is as shown below.
With the same SUBTOTAL Formula, we can also calculate average with the only change in Function Number.
After applying the SUBTOTAL Formula result is as shown below.
For the next cell, apply the SUBTOTAL Formula again with Function Number 101.
After applying SUBTOTAL Formula the result shown below.
Hide a few rows, now observe the changes in the average. The average with function number 1 remains the same as still, it is considering the hidden quantity also. The average with function number 101 changed because it ignores the manually hidden data. Like this, you can try the rest of the function numbers and check the functionality of each as the basic concept is the same they also will work in the same way.
Example #3
Subtotal has one more advantage that it will not consider if any subtotals available in the range of data. We will see one example to understand better. Input formulas to perform SUM operation product wise like A, B, C and D. It is not mandatory that it should be product wise you can do by color wise also.
After applying the SUBTOTAL Formula the result is as shown below.
Use the SUBTOTAL Formula again to check the SUM of B.
After applying SUBTOTAL Formula the result is as shown below.
Use the SUBTOTAL Formula again to check the SUM of C.
After applying SUBTOTAL Formula the result is as shown below.
Use the SUBTOTAL Formula again to check the SUM of D.
After applying SUBTOTAL Formula the result is as shown below.
Use the SUBTOTAL Formula again to the next cell.
After applying SUBTOTAL Formula the result is as shown below.
Use the SUBTOTAL Formula again to the next cell.
After applying SUBTOTAL Formula the result shown below.
If we observe the above screenshots there is sum by-product wise and while performing the total sum, we included the category wise totals into the total sum (cells from 36 to 48) but it will not consider that subtotals (cells from 44 to 47). So, we can calculate the subtotals in between the data range without affecting the total sum value.
I hope you understand how to use the subtotal and the use of it.
Things to Remember About SUBTOTAL Formula in Excel
- While giving the function number do not give the number which is out of function number range as it is not predefined by excel we will get an error message. We should always give the number between 1 to 11 and 101 to 111 otherwise it will throw the error message #value.
- While using the division operation remember no number should be divisible by zero. That means x/0 is an incorrect format.
- If you apply the subtotal for horizontal data that is from A1 : F1 and hiding any columns will not impact the subtotal.
- While applying subtotal if any cells do not have data or non-numeric data the function will ignore those cells.
- Use the function numbers by understanding the functionality and use otherwise you might not get correct results as you expect.
Recommended Articles
This has been a guide to SUBTOTAL Formula in Excel. Here we discuss How to Use SUBTOTAL Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles-
