# 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.

## 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.

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.

### 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.