Facts about subtotal formula - visible and hidden cells

posted Feb 3, 2013, 3:27 AM by roberto mensa   [ updated Feb 3, 2013, 11:23 AM by Krisztina Szabó ]


There are some things to learn about Excel’s SUBTOTAL formula and some strange behaviours to remember when using it.

As you may know, you can use some functions when calculating subtotals on a list - so “total” does not mean total as the meaning of sum - it could be one of the 11 functions (listed in the Excel help) according to the parameter selected in the first argument of the formula. There are two* sets of parameters designed to include or exclude values hidden by the Hide Rows commands:
  • parameters from 1 to 11 are designed to include hidden values
  • parameters from 101 to 111 should ignore hidden values.
Important to know that both of the sets ignore any rows that are not included in the result of a filter.

The problem is that technically filters work with hiding rows. If a row is filtered out from a list, it becomes hidden, in VBA the .Hidden property of the range is True.
So if subtotal is expected to work as it is described in the Help, it should somehow make difference between the rows hidden by the filter and hidden by the Hide Rows commands.
Seems it is not the case - subtotal is not prepared to be able to separate the two kind of hidden rows.

On the below screenshot you can see a correct result while the small list from B5 to C10 is filtered. The result contains 3 rows. The second list from B15 to B20 is not part of the filter, and it contains 6 rows: both of the subtotalss give 6 because no row is hidden.


The subtotal in cell F14 gives incorrect result if you hide rows when the filter is active on the worksheet (remember, it should include the hidden values):



Deactivating the filter will NOT change the result immediately:



You have to re-enter the formula, start a full recalculation (Ctrl+Alt+F9) or save-reopen the file to have the correct result after deactivating the filter:



Based on our test results, we can say that subtotal first checks if the filter is active on the sheet or not. If the filter is active, ALL the hidden rows will be treated as out of the filter result, so none of them will be included into the subtotal calculation.
On a filtered list subtotal with the “include hidden” parameter will never give correct result.

Calculation of SUBTOTAL

Normally, any change in the Filter criteria will flag ALL the rows in the autofilter range as uncalculated (dirty). This makes all subtotals referring to these rows to be recalculated. (In the file you can find examples and you can test it.)
In the above example our subtotals in row 15 does not see rows of the filtered range, so they will not be recalculated when the filter is deactivated! That is why we see the incorrect result on Picture 3 however filter is not active on the sheet. After re-entering the formula or changing any cell in it’s referred range, it will be (re)calculated and will show correct result.
The same thing happens when the row is hidden first and the filter is activated after - the result is seemingly correct, but only until the formula is not recalculated.

Filter on Tables also causing errors

Only one filter could be applied on a worksheet, but tables have own filters. All the above mentioned are true in case of using the table filters. They hide the worksheet rows in the same way, and subtotal is aware of the existing table filter too.
We can see only one difference (which actually does not have effect on the calculation of subtotal formula): In VBA worksheet has a .FilterMode property, according to the help it is “True if the worksheet is in the filter mode”. While using the filter of a table, the worksheet is not in filter mode, the property is False and the status bar does not sign filter mode.
And one more thing to remember: based on the fact filter hides rows, it is not surprising that using more tables side by side on a worksheet (so they have common rows) will result conflict in the filters. Filtering on one table will open/close rows filtered on the other table.

Conclusion

The features of subtotal formula (i.e. operating on filtered list and ignoring hidden rows) make confusions when the user hides rows on a sheet with filter. Obviously it is not a good practice from spreadsheet development and data organization point of view but it is not mentioned in the manual it could not be done.

We shared a file with examples where you can test the calculation of SUBTOTAL with filters and with reference to different ranges.


* In Excel 2000 only the first set of parameters is available. A friend of us has this version and he was so kind to test it. He experienced exactly the same error as we see in later versions.

by FrankensTeam

Ĉ
roberto mensa,
Feb 3, 2013, 3:27 AM
Comments