Excel SUBTOTAL Function, Examples and How to Use Subtotal Formulas in Excel

Excel SUBTOTAL function

Excel Subtotal Formula – In Microsoft Excel, the Subtotal Function is included in the Math & Trig function category. This function is used to calculate the subtotal value of a list or database.

By using SUBTOTAL function In the excel formula, you can choose one of the statistical evaluation calculations from 11 other excel functions, namely AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, and VARP functions.

So in one subtotal function there are 11 excel functions that you can choose as needed.

Another plus, this function is able to calculate filtered data. Using the subtotal function, the rows of filtered values ​​are ignored.

So when you filter a list or database in Microsoft Excel, only the visible values ​​will be calculated by this Excel Subtotal function.

In other words, hidden cell values ​​will be ignored in statistical calculations.

Then how to use this excel Subtotal function in excel formulas?

How to Use Excel’s SUBTOTAL Function

In the excel formula, how to use the Subtotal function follows the following rules:

SUBTOTAL(KodeFungsi; Referensi1; [Referensi2]; …)

Information:

CodeFunction

The numeric code (1-11/101-111) that determines which excel function the subtotal will use. For more details, see the explanation of the function code used below.

Reference1

One or more excel reference cells/ranges for which the subtotal will be calculated.

[Referensi2]; …

Other references that are optional up to a maximum of 254 cell references/range excel.

Function Code in Excel SUBTOTAL Formula

The numeric code used in the FunctionCode argument in the Subtotal formula is one of the following codes:

1. Number 1/101: Replaces the AVERAGE function, used to calculate the average value.
2. Number 2/102: Replaces the COUNT function, used to count excel cells with numeric values.
3. Number 3/103: Replaces the COUNTA function, used to count non-empty excel cells
4. Number 4/104: Replaces the MAX function, used to get the maximum, highest or largest value.
5. Number 5/105: Replaces the MIN function, used to get the minimum, lowest or smallest value.
6. Number 6/106: Replaces the PRODUCT function, used to calculate the multiplication value of excel cells
7. Number 7/107: Replaces the STDEV function, used to generate the standard deviation of a population based on the number of samples.
8. Number 8/108: Replaces the STDEVP function, used to generate the standard deviation based on population figures.
9. Number 9/109: Replaces the SUM function, used to get the summation value of a given cell reference.
10. Number 10/110: Replaces the VAR function, used to estimate population variation based on a given sample number.
11. Number 11/111: Replaces the VARP function, used to estimate population variation based on population numbers.

Code 1-11: Ignore filtered values ​​but still manually calculate hidden excel data rows or cells (Hidden).
Code 101-111: Ignore all hidden row/cell values ​​either because they are filtered or excel cells that are hidden (Hidden) manually.

Excel SUBTOTAL Formula Example

Consider the following sample data:

In cell D11 the formula used is:

=SUBTOTAL(9;D2:D10)

Code 9 shows the use of the SUM function in the subtotal formula to reference the range D2:D10.

If you filter the data, for example, only displaying fruit type data, the results will be like the image below.

From the picture, the subtotal function only counts the number of visible cells or excel rows. The values ​​hidden by the excel filter are ignored.

For how to use excel filters, please read the following excel tutorial: How to Filter Data in Excel.

The same applies to code 109 in the subtotal formula as below.

=SUBTOTAL(109;D2:D10)

Codes 9 and 109 are both used to use the SUM function or to add data to the excel subtotal function/formula.

Then what is the difference between codes 9 and 109 in this Subtottal function?

At first glance, the two code numbers 1-11 and 101-111 do not make any difference. Because they both ignore filtered data rows.

The difference in the number code in this subtotal will be seen if we hide the rows of data manually through the hide-unhide menu either through the menu command Home tab--cell group--Format--Hide & Unhide as well as the hide-unhide command with the right mouse click.

Consider an example of using the formula/subtotal function in the two images below:

=SUBTOTAL(9;D2:D10)

=SUBTOTAL(109;D2:D10)

In the two pictures above, the data rows are hidden manually/not the excel filter results.

From the two pictures, it can be seen that there is a difference when using the number codes 9 and 109 for the Subtotal function. The excel subtotal using code 9 produces a total of 160, while the excel subtotal using code 109 produces a total of 65.

Besides having the advantage of only counting visible cell values ​​or ignoring hidden rows, another advantage of the Subtotal function is that it ignores other subtotal functions that exist in cell/range references.

Consider the example of the subtotal formula in the following data:

In the example image above, the total average (D14) uses the formula:

=SUBTOTAL(101;D2:D12)

Returns the same value as the following Average (D15) function:

=AVERAGE(D2:D4;D6:D8;D10:D12)

With this subtotal formula, of course, it will be easier for us to compile statistical calculations in Microsoft Excel because we don’t need to read adjustments which may increase the difficulty in processing data in Excel.

Thus our discussion of the function or subtotal formula in microsoft excel. Hope it is useful.

Greetings Excel mania! Lazy to learn manual work.