Use the SUBTOTAL (and AGGREGATE) functions to avoid double counting

- [Instructor] When you work with lists that have embedded subtotals in them, you sometimes run into some calculation issues, some double counting. In this worksheet, you see a list of states, here are the New England states. We've got some totals in column C and D and then a summary down here, for example. There's a total and one over here for two different years where we've got the population estimates. And this is true for the other states. Now, some of you might be familiar with the term and a feature called auto calculate in Excel. If I highlight these numbers, I can look in the formula bar down below and see the total, 14,727,584, the same as this function, no big surprise there.

If I click column D, we see the total for all the entries here. If you happen to have just a rough idea of what US population is, it certainly isn't 642 million. This is exactly twice for the 2015 estimate. What we're doing here is adding up not only these, but also these numbers, and we're doing this for each of the groupings of states here. So we'd like to be able to work around that somehow and if we write a formula, for example equal, sum, or start off just by pressing Alt + Equal, and instead of just focusing on those two cells, of course, we want all of column D.

And what's our formula give us? Same answer that we would have expected. It's adding up all those numbers and of course we're getting a double count here. Now, we could use the if function and some other tests here to only add up, for example, just these. Or, another way to do it, is possibly just add up the totals, wherever we see the word Total. Possibly using sum if, some combinations or sum ifs, those functions. But, it's going to be better if, when we have these subtotals like this, let's use a function called subtotal.

So instead of sum here, let's put in subtotal. subtotal function begins with, and if we just typed it like this, we get a prompt. There are 19 possible entries here and there are many entries there. We see one through 11 and then 101 through 111. And what these represent are different functions. Now many, many times, if you're going to be using the subtotal function, you will want to use the number nine, which means sum comma, and then focus on what you're adding. Now, we're doing this here, and we need to adjust all of these.

Now, there are five or six other ones. Let me make it a little bit simpler by simply using the Find and Select function. So, I want to replace every time we see S U M left parenthesis, I want to see subtotal followed by a nine and a comma. I want to replace all of these. So we made six of 'em. And just to check these out, there we are. Check out a few of them, at least we don't have to check them all, probably. There we are, they're all looking good. They're giving us the same answers as before.

And if we click column D, we're still seeing that 642,000. Let's now use the subtotal function here. This function is designed to not count subtotal within the range that we're looking at. To be a little bit more precise here, we want to do an addition, this case nine. Many, many times, as I suggested earlier, if you want to be using this function, you're going to be using nine or one, most commonly. And you'll quickly get used to that nine means sum, one means average. So, we want to total these entries here in column D.

Then what will the subtotal function do for us? It will not count, for example, D8 and D17, which contain the subtotal function. And the answer we get now is correct, we have not gotten a double count. And that's the total population, looking at all the entries here, while ignoring these entries here. If we wanted an average, simply change to that one. Now the average population of a state is probably very misleading because some states have about half a million and others are approaching 40 million, at least one of them is.

So if we put in one here, the average state population is that. Somewhat of a misleading figure, based on this example. But here too, we are not averaging in those larger numbers. And if we were to multiply that by 51, since we are using D.C., equal this times 51, we're going to get pretty close to that 321 million number that we had seen earlier. There we are, same number we saw before. So, we're doing an average there, this is multiplying it by 51. But there are potential other uses of this and also a different option here.

So let me remove this, change this back to a sum again. Now, what if we had hidden some data? Possibly, you've got to display or maybe you don't want to show the mid-Atlantic states, for whatever reason, right click and hide. We're still getting a total referring to all the data. Now, in some cases, that'll be totally appropriate, but maybe you've got a list here of just certain states and maybe you've got some kind of a sales meeting where you decided not to cover a certain region based on your breakout here. What's the population of the regions that we are showing? In that case, what we want to use here instead of nine is 109, because we've got some hidden data there.

And you wouldn't know that at first by seeing the display here. Now what's our total? 263 million. Now to verify that, I can click on each of these totals here, this one, move down, holding down the Control key, click this one and this one and this one and this one, each time, I'm holding down Control, and this one. What's our total at the bottom of the screen? 263,854,957, the same number we're seeing right here. So, when you use 109 instead of nine, you are ignoring data in hidden rows.

Possibly what you could also use here is a function called aggregate. And notice here, after typing aggregate and left parenthesis, you get some choices here. Now, these are similar, at least first, the first nine are similar, the others are different. But if we wanted to do a total here, going to push nine for sum, actually, type it in, of course, or tab it in, if you wish, comma, and now we get eight choices, zero through seven. Do we want to ignore subtotals, do we want to ignore hidden rows? We get a number of different options and combination of options.

So suppose we want to ignore hidden rows and subtotals, maybe we'll put in a three here, comma, and we're looking at that same data in column D. And that's the same answer we saw before. Let's bring back the hidden data, so I'll drag across rows nine through 19, right click and unhide, we're getting our 321 again. Now, we could have used a different number here, depending upon what it is we're trying to do. So after typing the comma, we get these entries again.

So, at various times, you might want to ignore subtotals and other uses of the aggregate function or just ignore hidden rows or in some cases both, so you get the different combinations here. If we want to ignore hidden rows, nested subtotal, and aggregate functions, what if we put in a one right now? Now we don't have any hidden rows, but we do have nested subtotal and aggregates, so isn't this the same as zero, maybe? So what happens now if we choose column D? Like before, we're getting that entry.

Some cases, some of these results are going to be the same, but be alert to that idea as well. Now, you may or may not have used a feature called subtotal. Here's the same list that we've seen over here. Let's say it's based on the same data, with no interim subtotals. Now they happen to be in the same order as the data here that do not include rows like this, of course. But if you were to use this feature and one of the other weekly tips so it's coming up in a few months, we'll cover this as well too. But if you went to the data tab and inserted subtotals here, based on the fact that there's a change in the region, you'd want to add these two populations here, click okay, you would automatically insert the subtotals.

And what does Excel do in these cases right here? As I zoom in on this, first of all. When we get our answers here, Excel has automatically put in the subtotal function for us. So if you have used the subtotal feature, this is not the subtotal function, although Excel puts in the subtotal function for us right here, this automatically puts these in. So if we were tabulating data from a list like this, and we use the subtotal function, there it is right there again, and we simply wanted the total there of our data out there, put in the nine, comma, put the column, enter, that's our total for this list here, the same number we saw before.

It's not formatted, but it's that 321 million. We'll format it quickly from the Home tab there. Comma button, decrease in decimals, that's the same number we saw before. But once again, we're using the subtotal function. So be aware of situations where, if you manually created entries like this, you might have to use that subtotal function if you're trying to get accurate totals as you're writing formulas the way we saw them being written here. And what if we were to use the sum function? I didn't use that over here. What if we used the sum function now on this list? Well, it's still in use right here.

What's happening, it's not picking up or recognizing that subtotal is there. You've got to use the subtotal function not only in the entries over here, as we saw, but also as we tabulate those, so we don't get that double count. A valuable tool. And by the way, if you are starting off with a list, this is the list where use the subtotal function, it's better, as I remove these, I'll go back to the Data tab and actually choose subtotal and remove all this. If you are working with data in Excel, I think a lot of you know, it probably is best to work with a list like this without manually inserting the subtotals.

It's just easier to work with, use the subtotal feature when needed. But, if you've got the manual entries and sometimes you've got lists like this that are huge, even though this may not be the preferred layout, be sure to be aware of the fact that you might get double entries unless you use the subtotal function.