Okay, so let's look at the Subtotal function. We use the Subtotal function a lot when we are using filtering and filtered lists because that's its function. Its job is to only calculate unhidden totals or unhidden functions. So when we have filtered lists, you'll remember that we hide a series of rows that don't match a particular filter, and then we'll have all this information and we'll be wondering, I wonder what the total of these things are. So for example, in front of us here, I've already filtered on Betsy's lip gloss sales in the west. So I've got three filters in place, and I've got an auto filter on. So I've got Betsy, I've got lip gloss as a product filtered, and I've got location, the west, as a filter as well. So I've got three filters in place. Obviously, I've got some sales amounts in those items, and I want to know what the total sales is. Now, if we were in the bottom of a column of numbers, let me just come down here to the bottom of my list here. If we were at the end here and we wanted to add up this list, we'd use our, one of our favorite little functions, Alt + = to do a sum, and that would calculate the sum of the column above us. Now, we don't wanna use a Sum function, but handily, the Alt + = shortcut still works even at the bottom of filtered list. And I'll show you how this works. If I do Alt + = now, you'll see that instead of using the Sum function, you'll notice it's used the Subtotal function instead. And now the Subtotal function's got two arguments. The first argument is this seemingly random number. So we've got the number nine appeared, and then the list of things that we want to add up. Now, if I just use my cursor keys to move around in my formula here and just delete the number nine so that you can see what is this number nine and why do we have it, you'll see that the number relates to the particular function that we're using. So if I put the number one, we'd be using the Average function. If we use the number two, we'd use the Count function, and so on. And you'll see that the number nine, as you might expect, is the Sum function. So I'm saying, it's the Subtotal function is saying, well, it's got several subfunctions it can use. The most common of which we are gonna use here. This the Subtotal function, the Sum function. So we're gonna have a nine. And then the range of values exactly like you'd normally have with a sum. So we're gonna hit Enter there. And so we know that Betsy's lip gloss sales in the west are 1999.5. Now, if I wanted to know what Ashley's lip gloss sales in the west was, all I would need to do is change the filter. Because the Subtotal function is always calculating visible cells, when I use the dropdown list here to go and change this to Ashley from Betsy, we'll see that the Subtotal at the bottom of my list also updates to indicate that Ashley's lip gloss sales in the west are 1171.3.

So the Subtotal function, really useful. I'm just gonna delete it. Rather than use the Alt + = to do this, let's imagine that we're gonna write this out manually. So just to show you that you can do either way. if I'm gonna write it out manually and use the equals key and starts typing Subtotal, it's important to realize I need to do this on an unfiltered list. Otherwise, what will happen as I start writing out my function and I go to select my sales amount column, you will see that what happens is, if you imagine, I won't select all of the rows from here, I might only be selecting some of the data. So it's important when I go in here and I'm gonna start writing out my Subtotal function, I'm doing it on an unfiltered list. So I've just done Control + Shift + L, and L twice there, just to remove all the filters. I'm gonna go down to the bottom of my column using Control + down, and I'm just gonna come down underneath here and I'm gonna write my Subtotal in function manually. So =Subtotal, open bracket, let's say this time we're gonna use the Average function. So let's say we want the average.

It's item 1, comma, and then the things that I want the average of. So that's our full list range. Close the bracket, press Enter. So the average sale is 126.9. Okay, so that's function in place. So let's use Control + up to go up to the top, and let's start putting our filter in. So let's say, back to Ashley's sales of mascara, let's do, (keyboard clacking) in the east.

And you can see our subtotal is updated and the average has gone down. So it's one 119.1 for Ashley's mascara sales in the east.

So Subtotal function, very, very useful, especially when we combine it with filters. In this case, I was doing it with an auto filter, but equally Subtotal functions work with advanced filters as well.