Excel Function Friday: Sum Filtered List With SUBTOTAL

The Excel SUM function does a great job of adding numbers on a worksheet, and it’s probably the first Excel function that you learned about.

subtotal01

But it doesn’t do such a great job if you’ve hidden some of the rows with a filter, or with the Hide Rows command. Even though some of the numbers are hidden, they’re still included in the total.

subtotal02

Ignore Filtered Numbers

With the SUBTOTAL function, you can solve that problem, and only sum the visible numbers. There are a couple of quirks with SUBTOTAL though, including one strange thing that I hadn’t noticed before.

After you filter the rows in a list, use the AutoSum button on the Excel Ribbon’s Home tab, to insert a sum function.

subtotal03

If the table has a filter applied, the SUBTOTAL function will be automatically inserted, instead of the SUM function.

subtotal04

When numbers are hidden with the filter, the SUBTOTAL function sums only the numbers in the visible rows.

subtotal05

Hiding Rows

If you hide rows by using the Hide Row command, those amounts might be included in the total, even if you use the SUBTOTAL function. In the screen shot below, row 3 is hidden, but the SUM and SUBTOTAL functions both show a total of 40.

subtotal06

The first argument in the SUBTOTAL function specifies which function should be used. As you can see in the list before, there are 11 functions, listed in alphabetical order, starting with 1-AVERAGE.

In Excel 2003, and later versions, the same set of functions is repeated, starting with 101 – AVERAGE.

If you use the arguments in the 100s, neither filtered rows nor manually hidden rows are included in the result.

subtotal05b

In the example where row 3 has been manually hidden, if the first argument is changed from 9 to 109, the manually hidden amounts are not included in the SUBTOTAL result. In cell E7, the result is 30, because the 109 argument has been used.

subtotal07

SUBTOTAL Quirks

In the example below, there is a filter applied, to hide the Pen Sets item. Then, row 4 was manually hidden. In theory, the SUBTOTAL(9 and SUBTOTAL(109 should return different results.

In this case, the SUBTOTAL functions in columns D and E both return the same result. Even though one row has been manually hidden, the SUBTOTAL function in D7 recognizes it as a filtered row, because it is hidden in a filtered list.

subtotal08

Another “feature” of filtered lists is that if you manually hide rows, they can be automatically unhidden, if you clear the filter. Excel doesn’t seem to distinguish between the two types of non-visible rows.

subtotal09

The final quirk, that I just discovered, is that a table below the filtered list is also affected by the filter status. In this example, the upper list is filtered, and a row is manually hidden in the table below. However, the SUBTOTAL(9 formula treats that hidden row as filtered, and doesn’t include it in the result. Strange!

subtotal10

Hidden Columns Are Included

Remember that only the Hide Rows command affects the SUBTOTAL result, not the Hide Columns command. Cells hidden with the Hide Columns command are included in the result, as Dick Kusleika mentioned in the comments in his Determine If Cell is Hidden in VBA blog post.

Other SUBTOTAL Oddities

Have you encountered any other SUBTOTAL oddities and quirks?

_______________

You may also like...

11 Responses

  1. sam says:

    The Subtotal Function can be made to return an “array of Filtered cells” which can then be processed by functions like Sumproduct to sum/count based on condition(s) on a filtered table.

    I have sent you a PM attaching a file which demonstrates this…

  2. Ninad says:

    Thanks for highlighting Debra. I never knew of this. Just a Q. Why not use the 100 series only ? What’s the benefit of having both 1 – … and 101 – …

    Regards,

    Ninad.

  3. Contextures Blog » Change Excel Function With SUBTOTAL says:

    […] Excel Function Friday: Sum Filtered List With SUBTOTAL […]

  4. Martin says:

    yesterday I created a PT with a Calculated Field named Revenue, where I put an IF saying (if = IF(Actual =0;Budget;Actual ).

    The problem appeared with the total for rows: it only sums the Actual figures, not the Revenue figures (in this case, just Q1, not the rest).

    I had to eliminate the total for rows and columns, and add the formula manually.

  5. Contextures Blog » Excel Function Friday: Subtotal and Sumproduct with Filter says:

    […] Excel Function Friday: Sum Filtered List With SUBTOTAL […]

  6. Maxiee says:

    If the cells being counted (non blank or count subtotal argument), and the cell values are obtained through a formula, the subtotal will not work because it counts the formulas in the cells.

  7. Carroll Rinehart says:

    Debra, Could you provide an example where you don’t want values that are filtered out to be summed with visible rows, and, you want to only to sum values where certain conditions are met in other columns. I’m thinking of a combination of SUBTOTAL and SUMPRODUCT?

  8. Pat says:

    This was extremely helpful. Thank you.

  9. Sonia says:

    Is there a way to use a subtotal in filtered rows where the cell returns a calculated field?
    Example: Sales $100
    Sales Returns $50 (composed of 3 fields)
    Discounts $10
    Net Sales $40 (calculated at Sales-Sales Returns – Discounts)
    If a filter one of the sales returns field, my subtotal Net Sales cannot calculate using a Subtotal(9,) function. Do you know of a command to do this?

  1. October 8, 2013

    […] couple of years ago, we looked at the Excel SUBTOTAL function, and saw how you could allow users to select the function they want. In the example shown below, […]

Leave a Reply to Martin Cancel reply

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