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;

 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.

DOWNLOAD WORKBOOK

Subtotal Hidden Values

***Values for the SUBTOTAL function_num:

Includes hidden values    Ignores hidden values    Function      
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

HELPFUL RESOURCE:

mrexcelXL

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Advanced SUMPRODUCT Function: Conditional Sum Another great way that you can use the SUMPRODUCT function is to create a conditional sum criteria.For example, you may want to find out how much sales were made above the $3,000 transactional level.  See how easy it is with this quick SUMPRODUCT example.DOWNLOAD WORKBOOK...
SUMIF Function: One Criteria & Sum Range The SUMIF function can also be used to apply the criteria to one range and sum the corresponding values in a different range.So if you have a list of Sales Reps in one list and their Sales in another list and want to Sum the sales of only one of the Sales Reps, then the SUMIF...
Advanced Sumproduct Function: Sum the Top 3 Sales ... This is probably the most advanced level a SUMPRODUCT function can reach and that is by including a nested array formula.In our example below we want to return the 3 Largest values from the North region and sum them up.  As we are asking our formula to perform multiple calcul...
Excel Subtotal Function – Avoid Double Count... What does it do?It returns a Subtotal in a list or databaseFormula 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...

DO YOU WANT TO GET BETTER AT EXCEL?
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 *