SUBTOTAL function: Formula and how to use the SUBTOTAL function in Excel

What is the SUBTOTAL function in Excel ? How is the SUBTOTAL formula used in Excel ? Let's find out together with TipsMake.com!

 

Microsoft Excel has many functions for calculating sums, and SUBTOTAL is one of them. However, Subtotal is different from the SUM function. It doesn't calculate a regular sum. Instead, it's used to calculate subtotals in lists or databases, calculate averages, and much more.

 

What is the SUBTOTAL function and what is it used for?

At first glance, you might wonder: What is the purpose of the SUBTOTAL function? After all, it's easy to find the 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 much 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 SUBTOTAL formulas.

SUBTOTAL works with other Excel features, such as cell filtering. If you filter a table by a value, the SUBTOTAL formula will update, recognize the filter, and exclude the corresponding cells. SUBTOTAL is also useful for ignoring hidden values, something other functions (like SUM) can't do.

Fortunately, you can use other functions as part of a SUBTOTAL formula. There are 11 different subtotal methods available, each matching an existing Excel function. If you want a SUM formula that works with filtered cells and ignores other SUBTOTAL formulas, then it's best to use SUBTOTAL, although a pivot table can work similarly.

 

Subtotal formula in Excel

The SUBTOTAL function works by calculating the sum 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 sum, and also works within the function's parameters (for example, ignoring other SUBTOTAL formulas).

The SUBTOTAL function has the syntax =SUBTOTAL(function_num, ref1, [ref2],…) . Where:

1. Function_num: The numbers from 1 to 11 and 101 to 111 specify which function will be used for calculations in SUBTOTAL.
2. Ref1, Ref2, …: One or more cells, or ranges of cells, to calculate the subtotal, up to a maximum of 254.

Note:

1. The SUBTOTAL function is designed to perform calculations for vertically aligned data columns.
2. If the arguments ref1, ref2,… contain the SUBTOTAL function, they will be ignored to avoid double counting.
3. If function_num is between 1 and 11, the SUBTOTAL function calculates including hidden values ​​in the dataset because the row containing that range is hidden. If function_num is between 101 and 111, the SUBTOTAL function only calculates for non-hidden values ​​in the dataset (excluding values ​​in hidden rows).
4. For data areas hidden by filters, SUBTOTAL will ignore them.

 

How to use the SUBTOTAL function in Excel

1. Calculate the sum of the filtered rows. 

To understand this type of summation, please refer to the article "Calculating the sum of filtered list values ​​in Excel" . Basically, the SUBTOTAL function in this case will be:

=SUBTOTAL(9,pham_vi)

The 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, if there are hidden rows, you must use SUBTOTAL 103 to accurately count the visible non-empty cells.

For example, with the data table above, rows 4 and 5 will be hidden. Using SUBTOTAL 3 or SUBTOTAL 103 will produce two different results.

 

We enter the formula, and Excel will automatically display a set of functions for you to choose from without having to remember any of them.

Using SUBTOTAL 3 will give you 3, including the hidden cells in the row.

As for SUBTOTAL 103 , it will only display the non-empty cells that we can see, ignoring the hidden rows.

 

3. Ignore values ​​in nested subtotal formulas.

For example, we will calculate the average total weight of fabric in warehouses A1 and A2.

The formula for calculating the average for warehouse A2 = SUBTOTAL(1,C2:C4) gives the result 19.

The formula for calculating the average for warehouse A1 = SUBTOTAL(1,C5:C7) results in 38.

However, when calculating the average total amount of fabric in two warehouses, the average result from those two warehouses will be ignored. We have the formula =SUBTOTAL(1,C2:C9) and the result will automatically exclude the previously calculated average.

Things to consider before using the SUBTOTAL function in Excel

Although SUBTOTAL has advantages, there are also some things you need to consider before you start using this function, including the following points:

1. As mentioned, using 1-11 for the function_num argument will ensure that SUBTOTAL includes hidden values, while 101-111 ignores them.
2. Using values ​​other than 1-11 or 101-111 will cause Excel to return a #VALUE! error. This will also happen with 3D cell references (where the same cell, across multiple worksheets, is referenced within a range).
3. When horizontal cell ranges like 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 fixed.
4. Using SUBTOTAL on filtered data ensures that hidden values ​​are always ignored, regardless of the function_num argument used.
5. SUBTOTAL can be used as a nested function, as part of other functions, including formulas containing IF functions.
6. If another SUBTOTAL formula is within the ref1 data range, SUBTOTAL will ignore and exclude it from the overall calculation.
7. Excel allows the use of up to 254 cell ranges in a SUBTOTAL formula, although only 1 cell range is needed for the formula to work.

 

Errors when using the SUBTOTAL function in Excel

When you calculate the SUBTOTAL function in Excel, you will often encounter some basic errors:

1. #VALUE!: A function identifier that is not in the range 1-11 or 101-111, or has a 3D reference (ref).
2. #DIV/0!: Occurs when a specific sum must be divided by zero (for example, calculating the average or standard deviation of a range of cells that do not contain numerical values).
3. #NAME? function name SUBTOTAL is misspelled.

Here are some tips on how to use the SUBTOTAL function in Microsoft Excel that you might not know.

Tip 1: Suppose you want to ensure all t-shirt colors are available in all sizes at either warehouse. You can follow these steps:

Step 1: Click Subtotal . Remember that this step adds more criteria to the current subtotal data.

Step 2: Select COUNT from the dropdown menu and Size from the ' Add subtotal field to ' field. Then, uncheck Replace current subtotals . When you click OK , you will get the following data table:

This helps ensure that you have a sufficient number of different sizes and can categorize the data in a way that doesn't require repeated operations.

Tip 2 : It's always helpful to categorize data by column.

Tip 3: This helps you include a label in the first line.

Tip 4: If you want to summarize the data, uncheck the box ' Summary below the data when inserting Subtotal.'

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 a sum of the given values. SUBTOTAL can calculate the total, average, quantity, maximum, etc. This Excel function can also include or exclude values ​​in hidden rows.

1. Purpose: To calculate the sum of a subtotal in a list or database.
2. Return value: A number representing a subtotal type.
3. Argument:
   1. function_num- Some specifications specify which function to use to calculate the subtotal in the list.
   2. ref1- A range that is named or refers to a subtotal.
   3. ref2- [Optional] A named range or reference to the subtotal.

Using the SUBTOTAL function can help you quickly analyze a dataset without relying on more complex summary tables. If you're having trouble, you can also use the Subtotal feature, found in the Data tab on the ribbon, to automatically generate a SUBTOTAL formula that you can then edit or copy elsewhere.

See more:

1. The SUMPRODUCT function in Excel: Calculates the sum of the products of corresponding values.
2. How to merge two columns (First Name and Last Name) in Excel without losing content.