SUBTOTAL function in Excel What is that? SUBTOTAL formula in Excel how? Let's find out with Quantrimang.com!
Microsoft Excel has many functions used to calculate totals, and SUBTOTAL is one of them. However, Subtotal is different from the SUM function. It's not a normal sum. Instead, it is used to calculate subtotals in lists or databases, calculate averages, and more.
Main content about SUBTOTAL function
What is the SUBTOTAL function and what is it used for?
At first glance, you may wonder: What is the purpose of the SUBTOTAL function? After all, it's easy to find a subtotal from a range of cells using existing functions like SUMIF or AVERAGE.
While it's true that you don't necessarily need to use SUBTOTAL, it makes analyzing targeted data a lot easier. You can insert a formula using SUBTOTAL into a range of cells without affecting the overall total, because SUBTOTAL ignores other cells that contain a SUBTOTAL formula.
SUBTOTAL works with other Excel features, such as filtering cells. If you filter a table by a value, the SUBTOTAL formula updates, recognizes the filter, and excludes the corresponding cells. SUBTOTAL is also useful for ignoring hidden values, which other functions (like SUM) cannot do.
Luckily, you can use other functions as part of a SUBTOTAL formula. There are 11 different subtotal methods available, each suitable for an existing Excel function. If you want a SUM formula that works with filtered cells and ignores other SUBTOTAL formulas, it's best to use SUBTOTAL, although a pivot table can work the same way.
SUBTOTAL formula in Excel
The SUBTOTAL function works by calculating the total value of a range of cells, based on another mathematical function. SUBTOTAL uses up to 11 different mathematical functions (from AVERAGE to VAR.P) to find the appropriate total value, while working within the function parameters (i.e., ignoring other SUBTOTAL formulas).
The SUBTOTAL function has syntax =SUBTOTAL(function_num, ref1, [ref2],…). In there:
- Function_num: The numbers from 1 to 11 and 101 to 111 specify which function will be used to calculate in SUBTOTAL.
- Ref1, Ref2, …: 1 or more cells, or range of cells to calculate subtotals, maximum 254.
Note:
- The SUBTOTAL function is designed to calculate vertical columns of data.
- If the arguments ref1, ref2,… contain the SUBTOTAL function, they will be ignored to avoid double counting.
- If function_num is from 1 to 11, the SUBTOTAL function calculates including hidden values in the data set because the row containing that range is hidden. If function_num is from 101 to 111, the SUBTOTAL function only calculates non-hidden values in the data set (not counting values in hidden rows).
- For data areas hidden by Filter, SUBTOTAL will be ignored.
How to use the SUBTOTAL function in Excel
1. Calculate the total of filtered rows
To better understand this type of sum, please refer to the article Calculate total filtered list values in Excel. Basically, the SUBTOTAL function in this case will be:
=SUBTOTAL(9,_range)
pham_range here is the area you want to sum after filtering the data.
2. Count the filtered cells that are not empty
We will use SUBTOTAL 3 or SUBTOTAL 103. However, in case there are hidden rows, you must use SUBTOTAL 103 to accurately count the visible non-blank cells.
For example, the above data table will hide 2 rows 4 and 5. When using SUBTOTAL 3 or SUBTOTAL 103, it will produce 2 different results.
We enter the formula and then Excel will automatically display the function set for you to choose from without having to remember.
Results when used SUBTOTAL 3 will return 3, including hidden cells in the row.
As for SUBTOTAL 103 will only show the non-empty cell we see, ignoring hidden rows.
3. Ignore values in nested Subtotal formulas
For example, we will calculate the average of the total kilograms of fabric in warehouses A1 and A2.
Recipe calculate average for warehouse A2= SUBTOTAL(1,C2:C4) and gives the result 19.
Average formula for inventory A1 = SUBTOTAL(1,C5:C7) The result is 38.
However, when calculating the average of the total number of fabrics in 2 warehouses, the average results in the 2 warehouses will be ignored. We have the formula =SUBTOTAL(1,C2:C9) and the result will automatically remove the previously calculated average result.
Things to consider before using the SUBTOTAL function in Excel
While SUBTOTAL has its advantages, there are some things you should consider before you start using this function, including the following:
- As mentioned, using 1-11 for the function_num argument will ensure that SUBTOTAL includes hidden values, while 101-111 ignores them.
- Using a value other than 1-11 or 101-111 will cause Excel to return the #VALUE! error. This will also happen for 3D cell references (where the same cell, across multiple sheets, is referenced in a range).
- When horizontal cell ranges such as A1:D1 are used, hidden values are automatically included (regardless of the function_num argument value). This is a limitation of the SUBTOTAL function and cannot be overcome.
- Using SUBTOTAL on filtered data will ensure that hidden values are always ignored, regardless of the function_num argument used.
- SUBTOTAL can be used as a nested function as part of other functions, including formulas that contain an IF function.
- If another SUBTOTAL formula is within the data range ref1, SUBTOTAL ignores it and excludes it from the overall calculation.
- Excel allows up to 254 cell ranges to be used in a SUBTOTAL formula, although only 1 cell range is needed for the formula to work.
How to error when performing SUBTOTAL function in Excel
When you calculate the SUBTOTAL function in Excel, you will often encounter some basic errors:
- #VALUE!: The function identifier is not in the range 1-11 or 101-111 or has a 3D reference.
- #DIV/0!: Occurs when a specific sum must be divided by 0 (for example, calculating the average or standard deviation of a range of cells that does not contain numeric values).
- #NAME? SUBTOTAL function name is misspelled.
Some tips on how to use the SUBTOTAL function in Microsoft Excel that you may not know
Tip 1: Let's say you want to make sure all t-shirt colors are available in every size in either warehouse. You can follow these steps:
Step 1: Click Subtotal. Remember that this step is to add more criteria to the existing subtotal data.
Step 2: Choose COUNT from the drop-down menu and Size magnetic field”Add subtotal field to”. Then, uncheck Replace current subtotals. When clicked OKyou will get the following data table:
This helps ensure that you have a sufficient number of different dimensions and can classify the data in a way that does not require repeated operations.
Tip 2: Always useful when needing to sort data by column.
Tip 3: Helps you include a label in the first row.
Tip 4: If you want to summarize data, uncheck the box “Summary below the data when inserting Subtotal.” – Summary below data when inserting SUBTOTAL function.
In short, what is the Subtotal function in Excel?
As the name suggests, the SUBTOTAL function in Excel is used to calculate subtotals. The Excel SUBTOTAL function returns aggregate results for the values provided. SUBTOTAL can calculate sum, average, count, maximum… This Excel can also include or exclude values in hidden rows.
- Purpose: Calculate the subtotal in a list or database.
- Return value: A number representing a type of subtotal.
- Argument:
function_num
– A number that specifies which function to use to calculate subtotals in a list.ref1
– A named range or reference to a subtotal.ref2
– [tùy chọn] A named range or reference to a subtotal.
Using the SUBTOTAL function can help you quickly analyze a data set without relying on more complex pivot tables. If you get stuck, you can also use the Subtotal feature, found in the Data tab on the ribbon, to automatically create a SUBTOTAL formula that you can then edit or copy elsewhere.
See more: