- [Professor] All right, if you're using Google Sheets,
and you have a table of data like we have right here,
and you're trying to add the values.
Typically, you'll start with the SUM formula,
but we're going to go over some situations
where the SUM formula probably isn't the best choice.
So in this example we have several subtotals here,
and then we come down and we add the entire column.
And this is going to be an easy illustration
of when another function, called SUBTOTAL,
is going to do a better job for you.
So all of the bolded amounts
are SUM functions in this example.
And so this SUM function
is total in the first quarter sales in 2017,
second quarter sales, etcetera.
And then you come down to the bottom
and you want to add up the entire column.
But at this point,
the problem is that the SUM totals are inside of it.
So if you just do regular SUM function,
it will catch all the subtotals
and it will double the amount.
Okay, so if I hold down the Ctrl key and I just select
only the amounts that I'm really trying to sum,
and I come down to the lower right-hand corner.
If I change this to sum,
you can see that it's supposed to be $2,081.
All right, so we've accidentally doubled the amount.
So what you could do is you could come into the SUM formula,
and you could just pick the ranges
where you know there are no subtotals.
So I would select that range. Use a comma.
I keep going here.
I'm just selecting them with my mouse.
And that works.
That gets you to the right total,
but it's a little bit more fragile, right?
Doing a function this way
is going to make it more likely to break.
So this will be in the number one reason not to use SUM
is if you have subtotals in your range.
So I'll show you the solution.
I'm going to unhide.
Hide G again because that's gonna come a little bit later.
We're going to replace all five SUM functions
with something called SUBTOTAL.
All right, so if you come into the cell
and you type SUBTOTAL, the first thing that it asks you
is what the function code is.
So SUBTOTAL is a little bit unique,
where it can do different types of operations
depending on what number you give it.
The nine happens to be for SUM,
but if I left click on learn more, come over to the right.
You can also use this over
for the same concept of averaging or counting,
or doing some statistical analysis.
All right, so it'd be pretty common
where you would want to come in here and count, right?
And then you want to have a total count,
and the exact same problem is going to happen.
But in this case,
we're just going to do SUM to keep it simple.
So, the code nine is for SUM.
All of these have been replaced with SUBTOTAL.
If you double click in it, you'll see that.
And we actually have to rebreak our SUM function here
to show the comparisons.
Let me replace that range with the entire range.
Hit enter.
And now you see both the SUM function,
I double click in it,
and the SUBTOTAL function are working on the same range.
They're both summing.
But the SUBTOTAL is aware of itself,
so it doesn't count other subtotals.
All right, so that's the first
and the most obvious advantage of using SUBTOTAL.
Next thing that we're going to do,
where we're going to show this,
that SUM and SUBTOTAL work differently,
and you may wanna choose SUBTOTAL is if you apply a filter.
All right? So I will select this data.
I'm purposely not going to select the total row
on the bottom because that's what I'm going to use
to show the difference.
Go to Data and create a filter.
And let's just say we only want to look at the sales
of the furniture, right?
So we will uncheck the blanks, the technology,
and the warehouse supplies.
We'll left click on OK.
And this filter the list so we're only seeing furniture.
So you may be used to your SUM total is not updating.
So if you look in this, it still has the same range in it,
and it's still summing that range.
So that may be the behavior that you want.
But the SUBTOTAL function is aware of what's hidden,
so it is only summing what's visible.
So if I take my Ctrl key, I select these four cells,
you can see the sum of those four cells is 253.03.
And the last of the three reasons
why the SUM function might not work for you
is let's manually hide a row,
so we'll right click on row 16, left click on hide row,
and you see this 253 did not update.
So the function number nine for SUBTOTAL
knows not to sum the filtered rows,
but it doesn't know not to sum the manually hidden rows.
So we will unhide my last column
and you'll see these are the three different amounts,
all right?
So if we'll left click in this SUBTOTAL,
what we've done is use a function 109,
which excludes filtered values and manually hidden values.
So if you come over to the help
you can see that any of these functions,
if you add a one or a one zero before it,
so in other words, you make it into a 102 or a 111,
it will skip the manually hidden cells as well.
All right, so I'll take this filter off
by just selecting all of them.
Gone okay.
And the next thing that we'll show you
in this upcoming video
is that if you just want to group your data visually,
so that you can expand and collapse different areas of it.
Thanks for coming to watch the video,
and I hope to see you in the next one.