The SUBTOTAL function when data is Hidden

You want to subtotal a column. Great, there is a function for that. It's SUBTOTAL and the concept behind it is that it doesn't add the already existing subtotal values in the column (no double-calculations).

But now, you want to hide a few rows of data. Your subtotal value did not change. It remains the same.

You may need to change the SUM function number in order to do that (sometimes things are not that intuitive in Excel).

SUBTOTAL(9, etc. . .) will subtotal the column as is even though you've hidden a few rows (include if hidden).

SUBTOTAL(109, etc. . .) will subtotal the column and ignore what has been hidden (ignore if hidden).

No, sometimes things don't seem that intuitive in Excel, but thank goodness for Excel experts like us around the planet to point out the not-so-obvious.

 A video tutorial on this topic is also available on the 'Free Resources' page.

Write a comment

Comments: 0