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)

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


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.


Subtotal Hidden Values

***Values for the SUBTOTAL function_num:

Includes hidden values     Ignores hidden values     Function      
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP


How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

OFFSET Function Introduced What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number of ro...
Advanced SUMPRODUCT Function: Sum Multiple Criteri... What does it do? It returns the sum of multiple criteria from the corresponding ranges or arrays Formula breakdown: =SUMPRODUCT((array 1 criteria) * (array2 criteria) * array values) What it means: =SUMPRODUCT((find my criteria in this array) * (find my criteri...
Advanced SUMPRODUCT Function: Maximum Sales What makes the SUMPRODUCT function even more powerful is its ability to nest formulas, or in simple terms, add another function within the SUMPRODUCT function. Instead of getting the Total Sales for a region, we can extract the Maximum sales value simply by entering the MAX fu...
Total Bonus Due With An Array Lookup Formula The LOOKUP function has three arguments, it is a like a simplified VLOOKUP function: What does it do? It looks up a value (lookup_value) in one range (lookup_vector) and returns a value from the same position in a second range (result_vector) Formula breakdown: =LOOKU...

If so, join over 80,000 professionals who get career boosting, Free Excel lessons delivered on a weekly basis & receive our Free Excel Keyboard Shortcuts Template

Click here to subscribe

Leave a Comment

Your email address will not be published. Required fields are marked *