0609 Filter - SUBTOTAL() 9 vs. 109 Series

2 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$180.54
You save:  A$51.58
List Price:  ৳11,866.68
You save:  ৳3,390.72
List Price:  CA$176.88
You save:  CA$50.54
CHF 88.56
List Price:  CHF 123.99
You save:  CHF 35.42
List Price:  kr856.85
You save:  kr244.83
List Price:  €115.16
You save:  €32.90
List Price:  £102.10
You save:  £29.17
List Price:  HK$1,085.16
You save:  HK$310.07
List Price:  ₹10,216.03
You save:  ₹2,919.07
List Price:  RM564.15
You save:  RM161.20
List Price:  ₦53,196.20
You save:  ₦15,200
List Price:  kr1,178.92
You save:  kr336.86
List Price:  NZ$194.68
You save:  NZ$55.62
List Price:  ₱6,730.59
You save:  ₱1,923.16
List Price:  ₨22,489.39
You save:  ₨6,426
List Price:  S$185.22
You save:  S$52.92
List Price:  ฿4,192.56
You save:  ฿1,197.96
List Price:  ₺1,033.78
You save:  ₺295.38
List Price:  B$750.37
You save:  B$214.40
List Price:  R2,095.15
You save:  R598.65
List Price:  Лв225.25
You save:  Лв64.36
List Price:  ₩154,078.01
You save:  ₩44,025.43
List Price:  ₪458.17
You save:  ₪130.91
Already have an account? Log In


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

Sign Up


Share with friends, get 20% off
Invite your friends to TabletWise learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.