Excel SUBTOTAL Function (Table of contents)
SUBTOTAL in Excel
- A subtotal function is an inbuilt function in excel that is categorized under math/trig function
- Subtotal will not just total the numbers in a defined range of cells, it will help out by performing different arithmetic operations
- SUBTOTAL is versatile. You can use it to average, sum up, count a bunch of cells & other calculations
Definition
Returns a subtotal in a database or list i.e. To find out subtotal of a given range of cells. It performs a specified calculation for a given set of values.
SUBTOTAL Formula in Excel
The formula for the SUBTOTAL function is as follows:
SUBTOTAL(function_num, ref1, [ref2],…)Where
Function_num or operation code: It indicates which function should be used for the calculations of the subtotals within a given list or It is a type of mathematical operation that you like to perform
A function_num argument is applied based on the below mentioned two criteria or method
- Include hidden values When the function_num argument is between 1-11, the SUBTOTAL function will include the hidden values.
- Ignore hidden values When the function_num argument is between 101-111, the SUBTOTAL function will exclude or ignore the hidden values. It will find values only invisible rows ignores hidden values in the calculation.
| Fun_num Includes hidden values |
Fun_num Excludes hidden values |
Function | Description | |||
| 1 | 101 | AVERAGE | It calculates the Average of a specified range. | |||
| 2 | 102 | COUNT | It will Count the number of cells that contain numeric values. | |||
| 3 | 103 | COUNTA | It will Counts non-empty cells in the specified range. | |||
| 4 | 104 | MAX | It will find out largest value in the specified range. | |||
| 5 | 105 | MIN | It will find out smallest value in the specified range. | |||
| 6 | 106 | PRODUCT | It calculates the product of cells in the specified range. | |||
| 7 | 107 | STDEV | It estimates Standard Deviation in the specified range. | |||
| 8 | 108 | STDEVP | It calculates Standard Deviation based on the entire population. | |||
| 9 | 109 | SUM | It calculates the Sum of the specified range. | |||
| 10 | 110 | VAR | It estimates variance in the specified range. | |||
| 11 | 111 | VARP | It estimates variance based on the entire population. |
ref1, [ref2]: One or more references to cells containing the values that the calculation is to be performed for subtotal.
How to Use SUBTOTAL Function in Excel?
Let’s look out how the SUBTOTAL function works in Excel.
Example #1 – SUBTOTAL Formula for values Hidden using Excel Autofilter or Rows Hidden by a filter
In the below-mentioned example, the table contains Quarterly Sales Data, where individual quarters in column E & sales data in column F.
Sum function is applied in cell “F16” i.e. =SUM(F4:F15).
It returns or results in value 1184.
SUM function returns the same value. i.e. 1184, instead of 194 for Q4 data, if rows are hidden by a filter (Below mentioned screenshot).
If I want the Correct SUM value of Q4 data only, then I need to use the SUBTOTAL function.
Before applying the SUBTOTAL function, Under the data toolbar, apply a filter for the header row.
Now, Let’s calculate specifically Q1 or Q2 or Q3 or Q4 sales data individually for filtered data.
Use 109 as a function_num argument in SUBTOTAL function to Sum up the data. i.e. =SUBTOTAL(109,C17:C28)
The SUBTOTAL function ignores rows hidden by a filter and calculates the correct result.
Here subtotal value changes dynamically according to the filter, it will find a sum of values in visible rows only; hidden rows are neglected by SUBTOTAL function. (Below mentioned e.g.)
Q4
Q3
Q2
Q1
Example #2 – SUBTOTAL Function for manually Hidden Rows
In the excel sheet, row no 15, 16, 19, and 20 have been hidden using row formatting option, by highlighting these rows, right-clicking with the mouse and selecting Hide.
A subtotal function is used in cell C23 of the spreadsheet, to calculate the sum of visible cells.
We have to Use 109 as a function_num argument in SUBTOTAL function to Sum up the data.
i.e. =SUBTOTAL(109,C14:C21)
Here, SUBTOTAL function ignores manually rows hidden and calculates the correct result.
It will find a sum of values in visible rows only; hidden rows are neglected by SUBTOTAL function
Example #3 – SUBTOTAL Function for normal value
As all of the cells in the range of quarterly sales figures are visible, none of them are filtered or rows are hidden, it includes all the values.
In the below-mentioned example, multiple subtotal functions are applied.
In cell, C14, C15 & C16 SUBTOTAL function is applied.
To calculate SUM value, Function number 9 or 109 is used in a subtotal function where It calculates the Sum of specified range i.e. =SUBTOTAL (9, C4:C11)
It will give us the sum of all values in C4:C11, provided none are filtered & rows are hidden in that specified range.
To calculate MIN value, Function number 5 or 105 is used in a subtotal function where It will find out smallest value in the specified range. In a cell, C15, a SUBTOTAL function is applied, i.e. =SUBTOTAL(5,C4:C11)
It will find out smallest value in the specified range i.e. C4:C11
To calculate MAX value, Function number 4 or 104 is used in a subtotal function where It will find out the largest value in the specified range. In a cell, C16, a SUBTOTAL function is applied, i.e. =SUBTOTAL(4,C4:C11)
It will find out the largest value in the specified range i.e. C4:C11.
Things to Remember about the SUBTOTAL Function
- A SUBTOTAL function can be applied to find a sum of filtered values.
- A SUBTOTAL function is used to ignore values in hidden rows.
- Function _ num argument value should not be greater than 11 or less than 1, similarly should not be greater than 111 or less than 101, otherwise, it will give #VALUE! error.
- A SUBTOTAL function is applicable only for the numeric value.
- If there are any subtotals present in SUBTOTAL range, they will be neglected i.e. If any already existing SUBTOTAL formulas are present in the input range of another SUBTOTAL formula, then these values are neglected by subtotal function during calculation, so that double counting is avoided & it prevents the error of double counting.
- A SUBTOTAL function can be used to dynamically summarize data.
- Blank cells and cells containing non-numeric values are ignored by subtotal function during calculation.
Recommended Articles
This has been a guide to SUBTOTAL. Here we discuss the SUBTOTAL Formula and how to use SUBTOTAL Function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
