Excel Function Friday: Sum Filtered List With SUBTOTAL

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.

But SUM might not be the best function to use in all situations where you need a total.

Sum a List

In the screen shot below, there is a SUM formula in cell C7.

The result is 40, which is correct, for the 4 rows, with 10 units each.

subtotal01

SUM For Filtered List

The SUM function 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.

Instead of a sum of 40, it would be better to get a result of 30, for the 3 visible rows.

subtotal02

Ignore Filtered Numbers

If you use the SUBTOTAL function, instead of SUM, 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.

Use AutoSum Command

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

subtotal03

Usually, Excel inserts a SUM formula when you click the AutoSum command.

SUBTOTAL Function Inserted

However, if the table has a filter applied, the SUBTOTAL function is automatically inserted, instead of the SUM function.

subtotal04

Filtered Rows Ignored

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

subtotal05

Hiding Rows

Here’s something to be aware of, when you use SUBTOTAL.

  • 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

SUBTOTAL Functions

The first argument in the SUBTOTAL function specifies which function should be used.

  • As you can see in the list below, 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

Hidden and Filtered Rows Ignored

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 function oddities and quirks?
_______________

12 thoughts on “Excel Function Friday: Sum Filtered List With SUBTOTAL”

  1. 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. 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. […] Excel Function Friday: Sum Filtered List With SUBTOTAL […]

  4. 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. […] Excel Function Friday: Sum Filtered List With SUBTOTAL […]

  6. 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. 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. 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?

Leave a Reply to Contextures Blog » Excel Function Friday: Subtotal and Sumproduct with Filter Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.