Simple Formula and Instructions for Using the SUBTOTAL Function in Excel

Introducing the SUBTOTAL function

Excel SUBTOTAL function formula

=SUBTOTAL(function_num, ref1, [ref2],…)

Parameters of the SUBTOTAL function:

• Function_num: With numbers from 1 to 11 and 101 to 111. Used to determine which function will be used to determine conditions in the SUBTOTAL function.
• Ref1, Ref2, …:  Up to 254 cells (cell ranges) used to calculate the subtotal.

Notes when using the SUBTOTAL function in Excel

• The SUBTOTAL function is used to calculate totals for columns of data (vertically)
• To calculate subtotals, the Ref1, Ref2,… arguments that contain the SUBTOTAL function will be ignored.
• The SUBTOTAL function will calculate including hidden values due to rows containing hidden data ranges with Function_num from 1 to 11. The SUBTOTAL function will only calculate with existing values in the data set (excluding hidden row values) with Function_num from 101 to 111.
• The SUBTOTAL function will be ignored for hidden data ranges with Filters.

Applications and illustrations of how to use the SUBTOTAL function in Excel

To help everyone quickly grasp how to use the SUBTOTAL function in Excel, let’s go to the application and illustration part of the function. Using the Abe store inventory list as an example.

Count non-blank filtered cells

Task: Use the SUBTOTAL function to count how many items are out of stock.

To perform this task, we will apply the SUBTOTAL 3 or SUBTOTAL 103. You will use SUBTOTAL 103 when you do not want to count hidden data.

In this example, we will hide rows 5 and 6. When using the SUBTOTAL 3 and SUBTOTAL 103 functions, two different results will be achieved.

At the cell using the SUBTOTAL 3 function, enter the formula =SUBTOTAL(3,D3:D7).

2 will be the result when using SUBTOTAL 3, as it considers all hidden cells.

At the cell using the SUBTOTAL 103 function, enter the formula =SUBTOTAL(103,D3:D7).

1 is the result displayed by the SUBTOTAL function, as it does not count hidden cells.

Exclude existing values in nested SUBTOTAL formulas

When using the SUBTOTAL function, a cell range in the formula that has another nested SUBTOTAL function will be ignored. They only calculate cell ranges that do not contain a SUBTOTAL function.

Use the OGF store inventory list as an example.

Task: Use the above example to calculate the average value of each center A, B, and the average of both centers.

To calculate the average, you need to select the AVERAGE function, which is equivalent to number 1 or 101.

Here, I entered the SUBTOTAL formula in the cell containing the average result for center A =SUBTOTAL(1,C3:C5).

The average result for center A is 50.667.

Enter the formula in the cell containing the average result for center B =SUBTOTAL(1,C7:C8)

The average result for center B is 72.5

To calculate the average of both centers, you also perform a similar formula =SUBTOTAL(1,C3:C9) in the cell containing the result.

Previous average results of center A and B will be eliminated. They only calculate cell ranges that do not contain a SUBTOTAL function. Therefore, the result is 59.4.

Errors when using SUBTOTAL function in Excel

When using the SUBTOTAL function to calculate in Excel, some common errors are:

• #VALUE!: Function_num is not within 1-11 or 101-111 or 3D reference is used as a reference (Ref).
• #DIV/0!: Error occurs when a sum is determined to be divided by 0 (e.g., calculating the average or standard deviation of a range of non-existent numeric values).
• #NAME?: Misspelled SUBTOTAL function.

Conclusion

It can be said that using the SUBTOTAL function in Excel can quickly calculate data without having to rely on complex summary tables. I hope through this article, you will have more new Excel tricks to serve your work more effectively.

Thank you for reading the article.

Refer to premium, genuine laptop models currently on sale at FPT Shop: Laptop Chính Hãng

• How to use the division function in Excel with simple examples
• How to calculate absolute value using the ABS function in Excel
• How to use the TRIM function in Excel