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
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

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 What Excel does? How to use formula in Excel?

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.

EXAMPLE DATA FOR SUBTOTAL FUNCTION

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.
STEPS TO FIND SUBTOTAL OF THE GIVEN DATA

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. 

EXAMPLE 2 : DATA FOR SUBTOTAL FUNCTION

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.
SOLUTION : SUBTOTAL USING COUNT FUNCTION

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.