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.
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.
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.
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.
Filtered Rows Ignored
When numbers are hidden with the filter, the SUBTOTAL function sums only the numbers in the visible rows.
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.
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.
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.
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.
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.
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!
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?
_______________
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…
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.
[…] Excel Function Friday: Sum Filtered List With SUBTOTAL […]
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.
[…] Excel Function Friday: Sum Filtered List With SUBTOTAL […]
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.
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?
Carroll, there is an example on my website that shows how to sum specific rows in a filtered list:
http://www.contextures.com/xlFunctions01.html#Visible
This was extremely helpful. Thank you.
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?