Let's take a quick look at some math functions now.
We have two sets of product data, both for five weeks and five products.
So, they're the same kind of shape.
We'd like to summarize these numbers.
So we start by saying =SUM.
And we'll select the numbers in week one for
products A through E, close our parenthesis and say, Enter.
And that gives us our nice total
that we're pretty happy we can rely on as correct.
I'm going to left click and drag the fill handle down to extend this.
Then in addition, because I know these formulas are relative and
the data's the same shape,
I can copy and paste it down here because I know that these will work
to keep consistent sums which is good.
I'm also going to go and summarize the numbers up above here as well.
So, we'll sum all of the items for product A and again we can left click and drag.
And because we know we have five products in the next area,
we can paste this data in here as well.
As you can see everything is working quite nicely.
Now, what we want to focus on though is the total, the grand total.
So how can we go about doing this?
Well there's a few different ways.
We could say equals A9 plus B17.
It's rather B9 plus B17 and hit enter and that will work out quite nicely.
And we can check from the IntelliSense that it actually is working for us.
Another way that we might try and
approach this, is to actually use the sum function to sum these numbers.
Your first intent might be to do something like this where we sum the entire range.
Say okay, but if you look at this number now, I see 10,887.
That's a little bit big.
When I take these two and actually sum those together, I come up with 5443.50.
Which is exactly half of this number.
The reason is, because this function is actually summing everything.
But these numbers here have already been summed into this area, and
these numbers have already been summed into this area.
So, essentially we end up with double, so that won't work for us.
Let's look at a way that does.
We can still use the SUM function by saying =SUM.
You'll notice in the IntelliSense we've got Number 1, Number 2,
so we can provide multiple options here.
Instead of using a range, we'll grab a specific cell.
We'll hit comma and it says what you want for number two, we'll say this one here.
Close our parenthesis and say enter.
We now get 4512, which you can see is the exact number that we're actually after.
So, let's drag this guy across here and say okay.
And I want to show you one more function here.
That's actually specifically targeted to subtotals and totals and
that is the subtotal function.
So, I'm going to hit tab to complete that.
And you'll notice that right away, it comes up with a list and
this is the function number that we want it to do.
And what we'd like, is we'd like number nine for sum.
You can also use this to average or count or max or whatnot.
But we're going to say number nine, so double click on that, and
it says that's the function number.
Now where's the reference that you'd like to subtotal?
Let's say, well, I'm going to take this entire set of cells,
close my parenthesis and say Enter.
And you're thinking 9839, that seems too high, well indeed it does.
Because again, it's exactly double the sum of these.
So you say, well why would you use subtotal then?
I mean it works the same as sum.
Not exactly, watch this now.
We're going to go and replace this sum with a subtotal.
We're going to use function number nine to sum and
we're going to sum the values up above.
Close our parenthesis and say equals but before I hit Enter here,
what I'd like you to do is I'd like you to watch cell E18.
When I hit Enter, that number drops and that's interesting.
Let's try this here as well.
We'll say, equals subtotal 9 comma and
we'll grab the cell range above us.
Close our parentheses, and say equals and now, we come to 4919.66.
So, if I grab these two cells and check, that is indeed the number.
What's happening here, is that subtotal works a little bit differently than sum.
What subtotal does is it looks at the entire range that you've selected and
it finds any cells that have subtotals.
And it ignores the cells that are fed into them.
So, that's kind of cool, because if somebody adds another value,
that hasn't been included in the sub total
it will get picked up in this number. As you can see here,
if I were to add 10 it increases.
Because it's outside of a range that's already been subtotaled, so that's cool.
Subtotal is a great function for using in those scenarios.
Now, I'd like to round the sum of the next two numbers off.
So, in this case here,
what I'm gonna do is I'm gonna type in equals round open brackets.
And it says, what's the number you'd like to round?
I say well, I already know that SUM of this comma,
this gives me the subtotal, so I close the brackets there.
So, I can use the SUM function as a number in my rounding input.
Then, I can say comma.
How many digits would I like to round it to?
And we'll say zero and close our parenthesis.
This is what we call formula nesting,
where we nest one function inside a formula.
We'll now say enter, you'll notice we get 4639.
If I take a look at these two, it's 4638.52, it's rounded up to 4639.
So, there's a few different functions, different ways we can use them
in order to do some basic summarization and math concepts inside Excel.