All right, let's look at some basic math functions here and
different ways we can total data.
We'll start with this one right here.
So what we're going to do so we're going to hit AutoSum, and
we'll sum it using the sum of B6 to B10.
All right, we could, of course, write this
formula manually with =sum(C6-C10).
We can also do this, we can say Alt =, and Alt =, and Alt = is a shortcut for
the AutoSum, which will actually give us sum of D6 to D10.
And naturally, of course, we could also use a fill handle and
copy this across or use Ctrl+C or Ctrl+V to get there as well.
Now, interestingly enough, we can also use Alt = when we're in a column to
the right-hand side here, and it will actually build our formula for
us looking to the left for a contiguous range of data.
And of course, once again, we could always =SUM,
write it manually in order to make it work as well, and so forth.
So there's a lot of different ways to be able to write these formulas to get this
stuff in here.
Now, I'm going to go and quickly cheat on this one a little bit here.
I'm going to copy Ctrl+C, Ctrl+V,
all of these sum functions down here across the bottom.
And in addition to that, I'm also going to go and put a sum function for
this data here in as well.
So I'm going to go and select all of this, and I'm going to say Alt =.
And you'll see that what's actually happened here is that I've selected
multiple cells and typed in Alt =.
And it actually gave me a sum function that is picking up each cell above
it all the way across.
So if you feel more comfortable writing them individually, that is totally fine.
If you want to get tricky, select multiple cells, say Alt =,
just look at the formula afterwards to make sure that it's doing
what you actually believe that it should be doing.
All right, now, what I really want to focus on here is the different
ways to add up these non-contiguous cells of data.
So we're going to start this way here.
What most Excel users learn at the beginning is a simple function
like this =, B11+B19, Enter.
Does it work?
Absolutely.
Let's take a look at another one.
= sum(, we know we can put in number one, number two.
We're going to grab this one, comma, and this one here, Enter.
It will also work and give us the answer that we're looking for,
which is great, okay?
Now, personally, if I'm going to be doing something else,
I would probably use the sum function in this way.
But I want to show you another function set as well that we can use for
some things.
But I'm going to have to do this just a little bit differently
than what I've done before.
So I'm going to actually do this in,
I'm going to do it right over here in column D.
I'm going to write a different function here.
This one here is going to be SUBTOTAL.
Now, SUBTOTAL comes back and asks you what function you want to work with.
I'm going to choose 9 for a SUM.
And notice that we can do averages and counts, all kinds of things.
But we're going to grab SUM, you're going to say comma, and it says,
what's the reference that you're looking to sum?
So I'm going to sum all of these, just like I would using a SUM function,
and it gives me the same result.
And we can actually prove that by saying Ctrl+C.
It's a fully relative formula, so I'm going to paste it here.
We're at 2809.36, we've got the same values, and it's picking up nicely here.
Why would I do that?
Well, it's because I can also use my SUBTOTAL on the very bottom row.
With a SUBTOTAL(9 for a sum, now I can pick up this entire range,
close parenthesis, and hit Enter, and I get 4,919.
Now, the interesting thing here is if you add 2,110 and 2,809, you get 4,919.
The question is, why would you do that?
And it comes down to this.
If I put $1,000 dollars here, notice that my grand total does not change.
But if I put $1,000 here, my grand total does.
It may not be picked up in my subtotals, but
because my SUBTOTAL9 is picking up this entire range, this gets picked up.
Remember, this guy here is only summing these two cells, and
they're only pointing at the data that's in here.
What's nice about the subtotal is, you point it to an entire range and
any subtotals that are in there don't get re-added back in with the original data.
Or at least the original data that feeds the subtotal is excluded,
which is kind of nice.
Another function that we might want to use is we might want to
say let's do a sum of this one and this one again.
But what if we wanted to round this off, because right now, it's at 4638.52?
Well, I'd like to round it to zero decimal places.
We can do this by actually nesting a function within a function.
So here's what I'm going to do, is I'm going to come to the formula bar,
and I'm going to wrap my SUM function with a ROUND function.
So I'm going to type in ROUND, that's the function name.
I'm going to open the parentheses, and
you'll notice that the ROUND function says, give me a number.
Well, I'm saying the number that I want is the sum of F19 or F11 and F19.
That's the first parameter.
I'm going to hit comma, and
then it asks me how many digits would I like to round it to.
And I'm going to round it off to 0 decimal places, and close the parenthesis.
And what you'll notice is that we now get 4639.00.
Now, I'm going to press Ctrl+Z and undo this for a second.
Notice 4638.52, I'm going to press Ctrl+Y to reapply the rounding function.
And there we go, it's now rounded off quite nicely there.
So there's a few different ways to actually go and sum these things.
We can sum them using the SUM function with non-contiguous ranges.
We can use things like the SUBTOTAL function here, which picks up things that
are being inserted in between,, and we can also round things off.
So that's some of the basic mathematics functions that you might be interested
when working with data to dynamically solve it or to dynamically sum it and
update it.
because the beauty of this, of course, is that if anything changes in here,
all these totals will update to be exactly right.