Hi, this session is about a query resolution which I have been coming across very often. And this is with respect to subtotal formula that you generally get to use under filtered list. The question is the subtotal. When you start using that formula subtotal, you get two sets of average, two sets of count sum and two sets of max and min formula. So what is the difference between one and 101? So let me make it simple.
Let me first use the nine, put a comma and use these three values as range and let me see what the answer is. It is as expected 15. Now I just made that bold so that once you copy paste it right, it also replicates the formula on the column before the similar data. Now what I'm going to do is in the second formula, I'm going to put 109 What is 109 This is the one which you are going to see another instance of some Under the subtotal formula, apparently the answers are same. Where is the difference? If I hide any one room manually, my emphasis is on the word manually.
If I hide the row manually notice, the subtotal nine option includes all the three values, but the subtotal 109 doesn't include the manually hidden rows. Now, you might ask me, do we need to take care of this while applying filter? Well, I will tell you that normally when you apply filter, you use this shortcut or filter button, and then you pick and choose the desired criteria, let's say in this case, top 10 we never manually hide the rules. This is a part of the filter engine that the rules which do not meet the criteria gets hidden automatically. So our discussion is restricted to the rules which are hidden manually. And that is where the difference between subtotal one through nine was line comes in.
Now, although just to be safe, I would advise that when you apply all equal to get the subtotal formula, it's safer that if you apply one zero line, and then press enter to get the result, this is just to ensure that in case there are any hidden rows, which are hidden manually, then is not taken into account while calculating these options. So yeah, the option lies with you. Normally nine is fine, but yeah, just to be safe. 109 that's for some and similar logic, or average, that would be 101. So that's the difference between subtotal nine verses 109