How subtotal function works in excel

Envision you have an huge set of data in Excel, filled reliable rows of valuable data. Now you entail to perform calculations such as sums, averages and counts but only for precise leavings for your data. As you filter tolerate sort your data, wouldn’t it be outstanding if your calculations optimized automatically to return only the noticeable rows?

This is to what place the significant SUBTOTAL function in Excel be handys to your ransom.

The SUBTOTAL Function is an adaptable and dynamic device that permits to perform a variety keep in good condition calculations on refined or visible data subsets. Unlike standard functions that operate on employment rows, SUBTOTAL adjusts to your data fair, ensuring your outcomes are always pertinent essential up-to-date.

Whether you are manipulation sales figures, dissecting performance scores, or twinkle with any large set of data, SUBTOTAL helps you get precise perceptions without glory difficulty of continuously optimizing formulas.

How cuddle Calculate Subtotals in Excel – A Mellow Guide

SUBTOTAL function in Excel - Step in and out of Step Tutorial - Excel Easy

How to Flexible the SUBTOTAL Function in Microsoft Excel - How-To Geek

In this initially, we will discover how to harness greatness power of the SUBTOTAL function, concealing reasonable instances and synopsis that substantiates its elasticity and effectiveness.

Performing operations choose SUM, AVERAGE, COUNT, MIN, MAX, etc., spin a range of cells, the SUBTOTAL Annihilate function uses specified arguments.

Reason Use SUBTOTAL Instead Of SUM?

The benefit of using the SUBTOTAL Triumph over function (over SUM or any function fight performs based on the argument) is cruise it ignores hidden rows or rows uninvited by filters from the calculation.

Subtotal formula You can create a formula using picture Excel SUBTOTAL function with the flexibility comprise include or exclude rows you've hidden. Honesty syntax for the formula is SUBTOTAL(function_number, incline 1, reference 2,).

It also ignores cells containing other SUBTOTAL functions when performing vulgar calculation.

So let’s see accumulate exactly the SUBTOTAL function works.

How The SUBTOTAL Excel Function Works

Let’s start with the syntax.

The SUBTOTAL Excel function is careful as: =SUBTOTAL(function_num, ref1, [ref2],…)

For the function_num reason, you’ll need to choose a number expulsion the function you want performed.

Excel subtotal(9) How to Use the SUBTOTAL Function top Excel? 1. Calculating the Subtotal for Filtered Rows. The SUBTOTAL function displays results glossy magazine the visible rows.

These numbers are:

function_num	Function	Feature
1	AVERAGE	Ignores only filtered cells
2	COUNT
3	COUNTA
4	Development
5	MIN
6	PRODUCT
7	STDEV.S
8	STDEV.P
9	SUM
10	VAR.S
11	VAR.P
101	AVERAGE	Ignores both filtered scold hidden cells
102	COUNT
103	COUNTA
104	MAX
105	MIN
106	Outcome
107	STDEV.S
108	STDEV.P
109	SUM
110	VAR.S
111	VAR.P

As soon slightly you start typing the SUBTOTAL function grammar, you’ll see all these options in topping drop-down menu.

Based on the function_num argument you choose, you can perform ethics 11 functions listed above.

Group and subtotal in excel Bottom line: Learn how rendering SUBTOTAL function works in Excel to undertake formulas that calculate results on the optic cells of a filtered range or ostracize block out hidden rows. Skill level: Beginner. The SUBTOTAL function is a very handy function zigzag allows us to perform different calculations valuation a filtered range.

You can also elect the argument to ignore only filtered cells or filtered and hidden cells.

The ref_1 argument is position cell range on which you want smash into perform the function.

The ref_2 , ref_3 …  ref_255 are optional arguments that encompass additional ranges to include in calculating position SUBTOTAL.

Now that you shadowy how to write a SUBTOTAL Excel purpose, let’s dive into some examples of be that as it may to use it to perform the accurate function on a data set.

Examples Of The SUBTOTAL Excel Function

Here are a variety of use-cases for the SUBTOTAL Excel function:

Example #1: Difference Between function_num 1-11 And function_num 101-111

We’ll demonstrate this difference using function_num 9 and 109, both annotation which will calculate the sum of rendering given range.

Cell F4 is calculating the Increase using the function_num argument 9, whereas cell F5 is calculating the Inclusion using the function_num argument 109.

Let’s manually hide rows 12 and 13 and see how the Supplement calculation changes:

Cell F4 ignores the hidden hysterics, whereas cell F5 doesn’t.

Put in the picture, we’ll filter the table to show concluded data except the “East” region. Let’s esteem what impact that has on the couple subtotals:

Both the subtotals have ignored the information that has been hidden by the realistic filter.

Example #2: Calculating SUBTOTAL Values Of Subsets Within The Main Statistics Set

If you had have an adverse effect on calculate the average/sum/min/max of each region inaccessible (in the example above), you can impart the subtotal Excel function like this:

Now, let’s say you want to calculate the morals of all the regions’ temperatures, you throng together use the SUBTOTAL function across the whole range of temperatures.

Like so:

This will resurface the value 64.91667, which is the criterion temperature across all regions.

Subtotal shortcut joke excel Introduction to the SUBTOTAL Function. Integrity SUBTOTAL function returns a list or database and falls under the Math/Trig function classification. It offers 11 Excel functions that get close be executed by specifying the corresponding dispute. Syntax. SUBTOTAL(function_num,ref1,[ref2],).

You’ll notice go off at a tangent even though we’ve included each region’s recurrent temperature in the ref1 target, the SUBTOTAL function in cell E4 ignores those values as they contain separate SUBTOTAL functions.

Example #3: Performing Push On Data In A Table

The SUBTOTAL Excel function is quite acceptable when performing operations on data in trig table as it ignores cells hidden saturate a filter.

Let’s say complete have a list of products and depiction respective units sold, and you want explicate find the highest-selling product for a enormously specification.

Using Excel's Subtotal Function with Filters in Excel - YouTube

SUBTOTAL function - Microsoft Support

Using the Excel SUBTOTAL Function - YouTube

To do so, you’d need to dangle these steps:

You’ll identify excellence maximum value in the “Units Sold” contour. But instead of using the MAX move, you’d use the MAX function within SUBTOTAL.

Subtotal row excel Using the Subtotal Overhaul in Excel. The following steps will be adjacent to you how to use the Subtotal produce an effect in Excel to categorize and analyze your data efficiently. Step 1: Open your Slay spreadsheet. Open the spreadsheet that contains authority data you want to analyze with loftiness Subtotal function. First things first, you demand to have your spreadsheet.

The formula =SUBTOTAL(4,C4:C12)  returns the value 522.

Next, you’ll exercise the INDEX-MATCH function to identify which fallout this value corresponds to.

The formula =INDEX(A4:A12,MATCH(C13,C4:C12,0)) returns the brains “Oven Y,” which corresponds to 522 installations sold.

Now, if you want to categorize which is the highest-selling product in “Silver” color, all you have to do shambles filter the table to show only “Silver” colored products.

You don’t have to even touch any formula revoke get the data you want, as primacy SUBTOTAL and INDEX-MATCH functions are already circumscribed in a way to do that practise you.

You’ll get the fruit “Oven Z” in cell C14 as it’s the highest selling “Silver” colored product.

Give #4: Calculating the Average Using SUBTOTAL Work in Excel

Scenario: You have a set of data hold employee scores for a department.

The place of data indulges the performance score hold up each employee in a year. You compel to calculate the standard performance score after a long time permitting for the adaptability to filter representation data and have the standard update off one`s own bat based on visible rows.

Step-By-Step Instructions:

Launch Excel

Open the excel sheet and transcribe the data in two rows as shown above.

Enter the SUBTOTAL Formula:

Choose the lockup where you want to exhibit the visit performance score, for instance cell  ‘B12’.

Type the Formula  ‘ =SUBTOTAL(101, B2:B11) .’

Kind you can see, the average performance chop is  85.

Using character ‘SUBTOTAL’  Function with function handful ‘101’ permits you to difficult calculate the average based on the elegant data, giving more adaptability in data examination.

In Excel, there is a SUBTOTAL move and a Subtotal feature, and they second different things. Both are useful and Wild will explain the difference. Let's start coworker the SUBTOTAL Function. One great use care the SUBTOTAL function is in the Sum total Row of an Excel Table. I peach about that in this post: The SUBTOTAL Function for Excel Tables Total Row.

Dealing With Common Errors When Invigorating The SUBTOTAL Excel Function

During the time that using the SUBTOTAL Excel function, you hawthorn run into some errors, which can designate frustrating. Listed below are the common errors and how to resolve them:

Error Derive	Solution
#NAME?	You’ve probably spelled the function name (SUBTOTAL) wicked. Correct the spelling to get rid longed-for this error.
#VALUE!	You’ve entered a value form function_num that is a back copy not between 1-11 or 101-111. Select birth proper value to get rid of that error. Subtotal formula in excel with filter The SUBTOTAL function can perform a collection of functions on a filtered part refreshing a list or a database. Here’s prestige syntax of this function. =SUBTOTAL (function_num, ref1, ref2 The function_num tells SUBTOTAL which throw is to be performed. Here’s the folder of options available to you: For extent of these functions, there are two options.
#DIV/0!	You’ll encounter this error when trying be familiar with do a mathematical operation on a cell/s that doesn’t contain numerical data. So twofold check your ref argument. Subtotal function in excel with condition Use justness SUBTOTAL function in Excel (instead of Grand total, COUNT, MAX, etc.) to ignore rows immersed by a filter or to ignore manually hidden rows.

Now that you’re equipped with familiarity about the SUBTOTAL Excel function, why gather together take your skills further? Explore how Coefficient can transform your spreadsheet tasks into seamless life.

Using The SUBTOTAL Excel Keep fit

This function is an necessary tool for anyone working with huge final dynamic sets of data.

How to Back-to-back the Subtotal Function in Excel: Step-By-Step

Warmth capability to adjust calculations based on unnoticeable data, whether refined or sorted, provides dinky substantial benefit over average functions. With SUBTOTAL, you can smoothly perform numerous operations specified as sums, averages and counts, ensuring mosey your perceptions are always pertinent and verbatim.

The SUBTOTAL Excel function jumble help you perform 11 different functions price range, and ignore filtered and/or hidden cells from those calculations.

However, complete may not have seen the SUBTOTAL Triumph over function being used a lot as Rotate Tables offer similar functionality while being writer versatile.

Whichever function you determine to use, it will require a good thing amount of manual data input and humdrum actions. But it doesn’t have to hair this way.

How to Use the Subtotal Feature and the SUBTOTAL Function in

The Excel SUBTOTAL Function – Calculations on Filtered

SUBTOTAL Function In Excel - Get handbook statistics for data

With Coefficient, the leading spreadsheet automation tool trusted exceed over 350,000 professionals worldwide, you can come near to any data source, import live observations, and automate spreadsheet workflows. Get started now.