Hi there, I hope you would have loved our previous videos on filter, the shortcut keys that you will be allowed to use that are expected to use while working filter. Now this time we are going to talk about the calculations the formula based calculations with the filtered list. So what I'm going to do first thing first, I pick up the data, Shift Ctrl write down, I press Alt, a T to activate the filter. Or else you can also go to data and quickly click on this filter button. So that will activate the filter option. Once that is done.

I'm going to pick up the amount column and go to the number of filters. Of course if you go to number filters, you get many more options which are numeric based in the sense that between something and something, you'll have the option here less than equal to greater than does not equal to. And right now to be able to explain and illustrate the formula which is associated with calculations under filtered list. I'm going to top 10 Of course, you can make this top five, but for the time being, I'm keeping it top 10. Now once you do top 10, it is the top 10 lists based on the numeric value. Now suppose if somebody asks you that you want the total of the top 10 values, wouldn't you be tempted to use the sum formula?

If you do so, and if you happen to choose all the values within that list, it gives you the total of all the values whether they are part of the filter or not, which means it is taking the values which are hidden inside the rules, for example, 16 and 75. And that gives you a huge result. course this is incorrect. So what is the alternative? If you want to sum under a filtered list, the easiest path to do so is you press the shortcut key or equal to and I press enter. I will get the correct total.

Now all equal to the same thing can also be done. If you were to apply a some option now a lot of people go to Home And then you find the AutoSum. They click on some, and you get the exact same formula, subtotal nine, and then after the range, enter, I get the correct total. Now you must be wondering what is the subtotal formula, I will tell you the first portion of subtotal formula which says function num. Now it appears to be a single digit, let me do one thing. I'll do that.

And I'll put one just, for example, the moment I put one, I get a drop down. And this drop down tells me a variety of values. For the time being, we'll focus only on the first 11 values. Now if you happen to see the list, you will notice that the sum has been marked with nine which means if you want the summation of all the filtered list values, you must put in nine if you want, average, put one you don't have to remember the list. It's just that you must remember the logic. If you delete the one, automatically the list appears and you can pick and choose for example, Max wood before Five will be min and one is definitely average.

So this is the average value amongst the top 10 values. So if you're wondering, what are the other options when we talk about the one zero series, I'll take these parts a little later in the next lecture probably a bit too much in a single session. So I'll come to that. But for the time being, let me also highlight your attention to one phenomenon which has seen people coming across and that could lead to some erroneous results. The right subtotal formula by hand, of course, and the right nine indicating it's the sum that they are looking for, but they go ahead and choose the entire selection manually. Now notice it is starting from 16 through and where is our actual data starting from nine through 16 through till what row 244 through.

It doesn't even take the extra rows which are hidden between 244 and do 64th row, which means in future if you extend the list to lead Top 30 clients and I do that I do that keeping the subtotal formula as it is I do that top 30 Okay, as I scroll down the list as I double click on the formula notice it has not included the value, which was falling outside the 244. So, a safe option is you press the Alt equal to to activate the subtotal formula. Now, you might tell me if there are blanks What do we do the all equal to might not capture the entire series of range. Now that you know the logic even if you apply or equal to just keep in mind that the starting points should include the first row of data, which in our case is ninth row, and in this case, 260 third is the last row of the value.

So I put e 16. manually and e 263. Being the last row in which the data exists, come what may you must include the entire range to be able to get accurate result, time and again, so There you go. This was subtotal formula in its purest and basic forms.