What does it do?
It returns a Subtotal in a list or database
Formula breakdown:
=SUBTOTAL(function_num, ref1)
What it means:
=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)
***Go to the bottom of this post to see what each value stands for
The SUBTOTAL function in Excel has many great features, like the ability to:
* Return a SUM, AVERAGE, COUNT, COUNTA, MAX or MIN from your data;
* Find the SUBTOTAL of filtered values;
* Ignore other SUBTOTALS that are included in your range, avoiding any double counting!
* Include hidden values within your data by entering the first argument function_num, as values between 1-11;
* Ignore hidden values within your data by entering the first argument function_num, as values between 101-111;
Table of Contents
INCLUDE FILTERED OR VISIBLE VALUES IN YOUR SUBTOTAL…
Whenever you have a list of data with a filter (CTRL+SHIFT+L) and want to Sum a column, entering a SUM function works fine but it gives you the wrong result whenever you select a filter within your data.
That is because a SUM function includes the hidden values in the calculation. Not to worry, SUBTOTAL to the rescue!
Using the SUBTOTAL function you can Sum your list of values, then filter your list which returns the Sum of only the visible or filtered values.
STEP 1: function_num: For the 1st argument, select a number from 101-111, which will ignore any hidden values in the SUBTOTAL calculation!
STEP 2: ref1: For the 2nd argument, select the range of values that you want to use in your SUBTOTAL calculation.
See how this is done by following this simple tutorial, plus you can download the Excel workbook to keep.
***Values for the SUBTOTAL function_num:
| Includes hidden values | Ignores hidden values | 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 |
