Which of the Following Functions Can Be Inserted by the Subtotal Command?

The tutorial explains the specificities of the SUBTOTAL office in Excel and shows how to apply Subtotal formulas to summarize information in visible cells.

In the previous commodity, we discussed an automated mode to insert subtotals in Excel past using the Subtotal feature. Today, you lot will larn how to write Subtotal formulas on your own and what advantages this gives to y'all.

• SUBTOTAL function in Excel - syntax and uses
• Superlative 3 reasons to use SUBTOTAL function in Excel
• Subtotal formula examples
• Excel SUBTOTAL not working

Excel Subtotal role - syntax and uses

Microsoft defines Excel SUBTOTAL equally the role that returns a subtotal in a list or database. In this context, "subtotal" is non just totaling numbers in a divers range of cells. Unlike other Excel functions that are designed to exercise merely ane specific thing, SUBTOTAL is amazingly versatile - it can perform different arithmetic and logical operations such as counting cells, calculating boilerplate, finding the minimum or maximum value, and more.

The SUBTOTAL office is bachelor in all versions of Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.

The syntax of the Excel SUBTOTAL function is every bit follows:

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

Where:

• Function_num - a number that specifies which function to use for the subtotal.
• Ref1, Ref2, … - one or more cells or ranges to subtotal. The commencement ref argument is required, others (upwardly to 254) are optional.

The function_num argument can belong to one of the following sets:

• 1 - 11 ignore filtered-out cells, simply include manually hidden rows.
• 101 - 111 ignore all hidden cells - filtered out and hidden manually.

Function_num		Function	Description
one	101	AVERAGE	Returns the average of numbers.
ii	102	COUNT	Counts cells that contain numeric values.
3	103	COUNTA	Counts not-empty cells.
iv	104	MAX	Returns the largest value.
5	105	MIN	Returns the smallest value.
6	106	PRODUCT	Calculates the product of cells.
7	107	STDEV	Returns the standard departure of a population based on a sample of numbers.
8	108	STDEVP	Returns the standard deviation based on an entire population of numbers.
9	109	SUM	Adds upwardly the numbers.
10	110	VAR	Estimates the variance of a population based on a sample of numbers.
11	111	VARP	Estimates the variance of a population based on an entire population of numbers.

In fact, there is no need to memorize all function numbers. As before long as you lot showtime typing a Subtotal formula in a prison cell or in the formula bar, Microsoft Excel volition display a list of available function numbers for you.

For example, this is how y'all tin can make a Subtotal 9 formula to sum up the values in cells C2 to C8:

To add a part number to the formula, double-click on it, and so blazon a comma, specify a range, type the closing parenthesis, and press Enter. The completed formula will wait like this:

=SUBTOTAL(nine,C2:C8)

In a similar manner, yous tin can write a Subtotal one formula to get an average, Subtotal 2 to count cells with numbers, Subtotal three to count non-blanks, and and so on. The post-obit screenshot shows a few other formulas in action:

Note. When you use a Subtotal formula with a summary part like SUM or AVERAGE, it calculates but cells with numbers ignoring blanks and cells containing not-numeric values.

Now that you know how to create a Subtotal formula in Excel, the main question is - why would ane want to accept the trouble of learning information technology? Why not simply apply a regular role like SUM, COUNT, MAX, etc.? Yous will find the reply right beneath.

Top 3 reasons to utilize SUBTOTAL in Excel

Compared to the traditional Excel functions, SUBTOTAL gives you the post-obit important advantages.

1. Calculate values in filtered rows

Because the Excel SUBTOTAL office ignores values in filtered-out rows, you tin use it to create a dynamic data summary where subtotal values are re-calculated automatically according to the filter.

For example, if nosotros filter the tabular array to show sales only for the East region, the Subtotal formula will automatically adapt so that all other regions are removed from the full:

Note. Because both part number sets (1-11 and 101-111) ignore filtered out cells, you can utilise ether Subtotal 9 or Subtotal 109 formula in this case.

2. Calculate only visible cells

Equally you recollect, Subtotal formulas with function_num 101 to 111 ignore all subconscious cells - filtered out and hidden manually. So, when yous utilize Excel's Hide feature to remove irrelevant data from view, use part number 101-111 to exclude values in hidden rows from subtotals.

The following example will assist you gain more understanding of how information technology works: Subtotal 9 vs. Subtotal 109.

three. Ignore values in nested Subtotal formulas

If the range supplied to your Excel Subtotal formula contains any other Subtotal formulas, those nested subtotals will exist ignored, so the same numbers won't be calculated twice. Awesome, isn't it?

In the screenshot beneath, the Grand Average formula SUBTOTAL(1, C2:C10) ignores the results of the Subtotal formulas in cells C3 and C10, as if yous used an Boilerplate formula with 2 dissever ranges Average(C2:C5, C7:C9).

Using Subtotal in Excel - formula examples

When you first encounter SUBTOTAL, it may seem complex, tricky, and fifty-fifty pointless. But once you get down to contumely tacks, you volition realize that information technology's non that hard to master. The following examples will show you a couple of helpful tips and inspirational ideas.

Example 1. Subtotal 9 vs. Subtotal 109

As you already know, Excel SUBTOTAL accepts 2 sets of functions numbers: one-11 and 101-111. Both sets ignore filtered-out rows, but numbers 1-11 include manually hidden rows whereas 101-111 exclude them. To better understand the difference, permit's consider the following example.

To total filtered rows, y'all tin can apply either Subtotal 9 or Subtotal 109 formula similar shown in the screenshot beneath:

But if have subconscious irrelevant items manually by using the Hide Rows command on the Home tab > Cells group > Format > Hide & Unhide, or by right clicking the rows, and and then clicking Hibernate, and now you want to total values only in visible rows, Subtotal 109 is the only option:

Other function numbers work in the aforementioned mode. For case, to count non-blank filtered cells, either Subtotal iii or Subtotal 103 formula will do. But only Subtotal 103 can properly count visible non-blanks if at that place are whatever hidden rows in the range:

Note. The Excel SUBTOTAL office with function_num 101-111 neglects values in subconscious rows, but non in hidden columns. For example, if you use a formula like SUBTOTAL(109, A1:E1) to sum numbers in a horizontal range, hiding a column won't affect the subtotal.

Example 2. IF + SUBTOTAL to dynamically summarize information

If you are creating a summary report or a dashboard where y'all have to brandish various information summary but you lot don't have space for everything, the following approach might be a solution:

• In ane cell, make a drop-down list containing the functions names such as Total, Max, Min, then on.
• In a prison cell next to the dropdown, enter a nested IF formula with the embedded Subtotal functions respective to the function names in the drop-down list.

For example, assuming the values to subtotal are in cells C2:C16, and the drop-downward list in A17 contains Total, Average, Max, and Min items, the "dynamic" Subtotal formula is as follows:

=IF(A17="total", SUBTOTAL(9,C2:C16), IF(A17="average", SUBTOTAL(1,C2:C16), IF(A17="min", SUBTOTAL(5,C2:C16), IF(A17="max", SUBTOTAL(4,C2:C16),""))))

And now, depending on what part your user selects from the drop-down list, the corresponding Subtotal function volition calculate values in filtered rows:

Tip. If all of a sudden the drop-downwardly listing and the formula prison cell disappear from your worksheet, be sure to select them in the filter list.

Excel Subtotal not working - common errors

If your Subtotal formula returns an error, it's likely to be because of one of the following reasons:

#VALUE! - the function_num statement is other than an integer between 1 - 11 or 101 - 111; or whatever of the ref arguments contains a 3-D reference.

#DIV/0! - occurs if a specified summary role has to perform a partition past zero (e.g. calculating an boilerplate or standard difference for a range of cells that does not contain a single numeric value).

#Proper name? - the name of the Subtotal function is misspelled - the easier fault to fix :)

Tip. If you don't feel comfortable with the SUBTOTAL function yet, you can apply the built-in SUBTOTAL characteristic and accept the formulas inserted for you automatically.

Finally, you are welcome to download the Excel Subtotal sample workbook containing all of the formulas we discussed in this tutorial.

You may also be interested in

robersonfortudieved96.blogspot.com

Source: https://www.ablebits.com/office-addins-blog/2016/08/16/excel-subtotal-function/

Share this post