Menu
PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
EXCEL FUNCTION-SUBTOTAL
INTRODUCTION
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.
PURPOSE OF SUBTOTAL FUNCTION IN EXCEL
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.
PREREQUISITES TO LEARN Round FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the rounding process.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
SYNTAX: SUBTOTAL FUNCTION
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.
EXAMPLES: SUBTOTAL FUNCTION IN EXCEL
EXAMPLE 1: FIND THE SUBTOTAL OF THE DATA USING SUM FUNCTION
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.
SOLUTION:
The usage of SUBTOTAL FUNCTION in excel is shown below.
STEPS TO USE SUBTOTAL FUNCTION IN EXCEL
- Select the cell where we want the result.
- Enter the formula =SUBTOTAL (FUNCTION CODE , REFERENCE 1, REFERENCE 2 ….. ON WHICH OPERATION IS NEEDED
- For our example, the formula used will be =SUBTOTAL(9,D7:D19) . [ 9 CODE IS FOR SUM FUNCTION ]
- Click OK.
- The answer will appear in the cell.
- Look at the picture below for real results.
- The result appeared as 11177 which is correct.
EXPLANATION:
The function used is =SUBTOTAL(9,D7:D19)
Let us understand the function step wise step.
The first argument in the function is 9.
If we refer to the function reference, we can see that 9 number code is given to the SUM FUNCTION which we need in this example. So, that is the reason for using the 9 number as code.
The second argument is a range from D7 to D19 , which contains our data.
The result is 11177 which is the total sum of all the numbers contained in the range.
EXAMPLE 2: FIND THE SUBTOTAL OF THE DATA USING COUNT FUNCTION
Now , let us try to use one more usage of the SUBTOTAL FUNCTION.
Let us try to find the subtotal of the data using the count function. Actually this function will give us the count of the data.
SOLUTION:
STEPS TO USE SUBTOTAL FUNCTION IN EXCEL:
- Select the cell where we want the result.
- Enter the formula =SUBTOTAL (FUNCTION CODE , REFERENCE 1, REFERENCE 2 ….. ON WHICH OPERATION IS NEEDED
- For our example, the formula used will be =SUBTOTAL(2,D7:D19) . [ 2 CODE IS FOR COUNT FUNCTION ]
- Click OK.
- The answer will appear in the cell.
- Look at the picture below for real results.
- The result appeared as 13 which is correct.
EXPLANATION:
The function used is =SUBTOTAL(2,D7:D19)
Let us understand the function step wise step.
The first argument in the function is 2.
If we refer to the function reference, we can see that 2 number code is given to the COUNT FUNCTION which we need in this example. So, that is the reason for using the 2 number as code.
The second argument is a range from D7 to D19 , which contains our data.
The result is 13 which is the total COUNT of all the numbers contained in the range.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.