=SUBTOTAL(9,range) versus =SUBOTAL(109,range)
I have been busy revamping CPASelfstudy.com's site design and working on some Excel Ebooks and unfortunately my blogging has fallen by the way side - temporarily.
I do plan to start writing again once or twice a week starting next week.
My husband has been showing me all the different things he has been doing with the subtotal function and filtering so that is probably what I will talk about next week. (And yes, we do need to get a life!)
One thing that puzzled me was that he was using a function =SUBTOTAL(109,range) instead of the function I was familiar with which is =SUBTOTAL(9,range) to sum and subtotal.
Sorry- my original post on the differences between =SUBOTOTAL(9,range0) and SUBTOTAL(109,range()) was incorrect.
Thanks Jan for the correction :
SUBTOTAL(9,...) totals everything except cells with subtotals and filtered cells.
SUBTOTAL(109,...) totals everything except cells with subtotals and filtered cells and hidden rows.
Software examples that you can easily understand and immediately apply to your own business needs.
You got this one wrong I'm afraid.
ReplyDeleteSUBTOTAL(9,...) totals everything except cells with subtotals and filtered cells.
SUBTOTAL(109,...) totals everything except cells with subtotals and filtered cells and hidden rows.
If you want to learn a bit more about SUBTOTAL function you can check this link
ReplyDeletehttp://prakashgusain.blogspot.com/2011/04/ms-excel-subtotal-function-and-filter.html
ATTENTION!!!
ReplyDeleteIn Excel help, it is WRONGLY stated:
«The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.»
Correctly, it would be:
«The SUBTOTAL function ignores any rows that are hidden whenever a filter is applied in the worksheet, even if the filter does not affect the rows where SUBTOTAL is reading values.»