SUBTOTAL Formula in Excel

Summarize Data With Dynamic Subtotals

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)

---

SUMMARIZE DATA WITH DYNAMIC SUBTOTALS…

The Subtotal function can become dynamic when we combine it with a drop down list.
This is a great trick and one that can be used when creating an Excel Dashboard that summarizes key data metrics on one page.

STEP 1:  We need to list the Subtotal summary functions in our Excel worksheet

STEP 2: In the ribbon select Developer > Insert > Form Controls > Combo Box

STEP 3: With your mouse select the region where you want to insert the Combo Box
STEP 4: Right Click on the Combo Box and select Format Control…

STEP 5: For the Input Range, you need to select the range with the Subtotal summary names from STEP 1
STEP 6: For the Cell Link, you need to select a cell where you want to show the output and press OK
(The Cell Link increments by 1 depending on the order of the list and the name chosen.  We will use this value as our first argument in the SUBTOTAL function)
STEP 7: Enter the Subtotal function and for the first argument function_num we will reference the Cell Link from STEP 6

STEP 8: For the second argument, select the data range

So you can see as you choose a summary name from the drop down list, it gives us a value for the Cell Link which is equals to the function_num for that summary name!

***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

Excel Subtotal Function – Include Hidden Values

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)

---

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;

 INCLUDE HIDDEN VALUES IN YOUR SUBTOTAL…

Sometimes you are faced with lots of data and just want to show the Totals row and hide all the individual rows that make up the Totals, just for presentation purposes.
Using the SUBTOTAL function you can Sum your list of values and then hide them from the worksheet without affecting the function.
STEP 1: function_num:  For the 1st argument, select a number from 1-11, which will include any manually hidden rows 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.
STEP 3: Highlight the values that you do not want to show on your worksheet and then Right Click and select Hide.
See how this is done by following this simple tutorial, plus you can download the Excel workbook to keep.

Excel Subtotal Function – Filtered or Visible Values

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)

---

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.