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)

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

SUBTOTAL filtered values

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.

DOWNLOAD WORKBOOK

Subtotal Visible or Filtered Values

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

HELPFUL RESOURCE:

mrexcelXL

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Vlookup in an Excel Table What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP(thi...
Excel Subtotal Function – Include Hidden Val... 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...
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...
Return the Last Value in a Column with the Offset ... 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...

DO YOU WANT TO GET BETTER AT EXCEL?
If so, join over 75,000 professionals who get career boosting, Free Excel lessons delivered on a weekly basis!

Click here to subscribe

Leave a Comment

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

  • Hi,
    I’m trying to get a cell to count how many times “0” and “1 sox” is displayed. I’ve got
    =SUMPRODUCT(SUBTOTAL(3,OFFSET(‘PMP Data’!E2:E19962,ROW(‘PMP Data’!E2:E19962)-ROW(‘PMP Data’!E2),0,1)),–(‘PMP Data’!E2:E19962=”1 sox”))+SUBTOTAL(2,’PMP Data’!E2:E19962)

    This counts when 1 sox is listed, however it also counts any random number?

    • Hey Nathan,

      Put your data in an Excel Table the create a Pivot Table.

      Drop the Values field in the Row Labels and also into the Values area of the Pivot Table.

      This will show you how many “0” and “1 sox” are within your data set.

      Thanks,
      John

  • Dear Sir,

    Please see here below mentioned query for your easy understanding.
    I want to see the result in column E7 when i filter data in column A3

    (Column E7) Vendor Name:

    (column A3) Vendor Name
    Abdul Aziz Fahad Al Hajri Est
    Ali-Ahmed-Al-Kanfari Gen. Co. Est.
    Amtar Al-Watan Const. & Dev. Est.
    Arab Al-Tamuz for Cont. Est.
    Arkal Est. for Cont.
    Danat Al-Rayan
    Gray Falcon

    • Hussain,

      Please use this formula here: =OFFSET(A3,1,0,1,1)

      I hope this helps!

      John