PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
The SUBTOTAL FUNCTION is used to find out the subtotal or a specified range within a database or bigger range.
Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
THE SUBTOTAL FUNCTION RETURNS A SUBTOTAL OF A LIST OR DATABASE. THE SUBTOTAL FUNCTION NOT ONLY SUMS UP THE DATA BUT OFFERS A NUMBER OF OPERATIONS ON THE SUB LIST.
Suppose, we have a long list of data and we want to find out the subtotal of a portion of the list. The Subtotal function is perfect for this operation.
We’ll see different examples to use the subtotal function.
In this article, we will learn about the purpose, syntax formula, example and other information about the SUBTOTAL FUNCTION.
THE SUBTOTAL FUNCTION RETURNS A SUBTOTAL [ OR RESULT OF ANY OPERATION FROM THE AVAILABLE LIST ] FOR A GIVEN LIST OR RANGE.
For the example,
If we have some data from A1 to A10 and we want to find out the total sum of A1 to A5 only, we can make use of Subtotal function.
The additional benefit of this function is the availability of different options of the operation within the same function.
FUNCTION NUMBER (INCLUDING HIDDEN VALUE) | FUNCTION NUMBER (IGNORE HIDDEN VALUE) | FUNCTION |
---|---|---|
1 | 101 | AVERAGE |
2 | 102 | COUNT |
3 | 103 | COUNTA |
4 | 104 | MAX |
5 | 105 | MIN |
6 | 106 | PRODUCT |
7 | 107 | STDEV |
8 | 108 | STDEVP |
9 | 109 | SUM |
10 | 110 | VAR |
11 | 111 | VARP |
The list gives the various codes for the different functions. The option for including the hidden values or ignoring the hidden values is also available.
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
The Syntax for the SUBTOTAL function is
SUBTOTAL (Function Number , Ref 1, Ref 2, …..)
FUNCTION NUMBER is the code for the function [Operation to be performed ] to be used.
Ref 1 Reference or Named Range on which we want to apply the subtotal function.
Ref 2 [OPTIONAL ] Additional range to be included.
Let us take a sample data to try Subtotal Function.
We need to find out the subtotal of the data using sum function.
The data is shown in the picture below.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE