Locked lesson.
About this lesson
Learn basic math functions including SUM, ROUND and SUBTOTAL.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Math Functions.xlsx9.7 KB Math Functions - Completed.xlsx
10.6 KB Math Functions - Extra Practice.xlsx
15.3 KB
Quick reference
Topic
Basic math functions including SUM, ROUND and SUBTOTAL.
When to use
Use to add multiple cells together and return totals and subtotals.
Instructions
Ways to use the SUM function
|
=SUM(A1:A10) |
Add up all cells from A1 through A10. (A1 up to A10.) |
|
=SUM(A1,A10) |
Add the individual cells separated by the commas. (Just A1 and A10.) |
|
=SUM(A1,A5:A10) |
Add A1 plus the range from A5 through A10) |
Key points to remember: There is no limit to how many cells or ranges you can add by appending cells or ranges separated by a comma.
The ROUND function
- Takes a number, plus the number of decimals places
- =ROUND(A1,2) Will round the value in A1 to two decimal places
Key points to remember: You should always round multiplication or division in Excel to make sure the value you see in the cell doesn’t have any hidden decimals that will influence your end result.
The SUBTOTAL function
- Has many different “totaling” options, such as SUM, AVERAGE, MIN, etc.
- =SUBTOTAL(9,A1:A10) sums up all numbers in A1:A10, unless they have been included in a previous subtotal.
Note: In addition to the ones covered here, there are many other math functions available in Excel.
Login to download- 00:04 Lets take a quick look at some math functions now.
- 00:05 Basic SUM and ROUND and we'll even take a quick look at SUBTOTAL. So what we have here is
- 00:10 we have a two month listing with the individual weeks and products and the actual sales in here and what we want to do is total these up.
- 00:17 So we can do this by using the SUM function. We'll go across first, this is real easy. Type in =SUM
- 00:24 open our bracket and ask for a number it also takes a range, so we can go and select that
- 00:30 close our bracket and hit Enter.
- 00:32 We get 1788.86 and drag this guy down.
- 00:36 And because it's a relative formula you'll notice that it is actually picking up each individual row as we go down.
- 00:43 I can also use the exact same formula
- 00:45 copy it, paste it down into the second month's section and now it gives me the same thing;
- 00:52 a nice relative formula that covers off from columns B to F, no matter what row its on. That's perfect.
- 00:58 Now, what about vertical? Well sure we can do that too. Say =SUM
- 01:04 Open our brackets we will select our range of numbers
- 01:08 close our bracket, hit Enter so sum B4 through B8, Enter: 2409, perfect,
- 01:14 and we will copy that across as well.
- 01:19 And now we can copy this entire row again because it's relative because we have
- 01:22 five weeks here we have five weeks here we know the date is the same shape
- 01:26 so we can paste that directly into row 17 as well and we now have a fairly summarized table already which is great.
- 01:34 Now here comes the questions though. What do we do with our grand total? How do we actually make this work?
- 01:40 Well we could just say
- 01:43 =B9+B17
- 01:47 and that would give us a total of 4736. No problem.
- 01:51 Could we use the SUM function for this? We could try =SUM
- 01:54 open our bracket, we will sum the entire range of data and see what happens here.
- 02:01 Close our bracket and hit Enter
- 02:03 and it comes back with 10,887.
- 02:06 Well plainly looking at 2610 and 2832
- 02:11 when I go and sum these two together you can actually see from my tool tip text down on the bottom here it's only $5443.
- 02:16 So why is it coming back with 10887?
- 02:21 The answer to that is that it's actually summing every single cell that makes this up
- 02:26 including the subtotals that come up with that value so plainly that's not going to work for us.
- 02:31 Is there an alternative for the SUM function that will actually not do that? Well there is, we can say =SUM
- 02:39 and you'll notice the tool tip text gives us number 1 and then comma and in square brackets it's got number 2. So 2 is optional.
- 02:46 So if we go select our first number and then we hit that comma you'll see that number 2 now goes bold, it says that's what we're working with
- 02:53 and we get an optional number 3. So we can keep carrying this chain on but I can actually go and click on this so I get sum, or D9,D17
- 03:01 Close the bracket and hit Enter
- 03:04 And it's $4500 which just happens to be the sum of these two numbers
- 03:08 So this is very similar to what we see here using =B9+B17 this is just the way that we do it using a SUM function
- 03:16 =SUM(D9,D17) then you could put comma something else comma something else to add more. So just copy that across
- 03:23 Now what if we wanted to go and use the SUBTOTAL function?
- 03:28 Well the way that SUBTOTAL works is we take SUBTOTAL, it has a function number
- 03:33 so 9 is SUM. There's also all kinds of different ones but if I say 9,
- 03:39 oops, missed that. Let's say 9 comma
- 03:42 and we sum our entire range
- 03:46 What you'll see now when we close the brackets is it comes back with 9839 which
- 03:51 just happens to be, again if I select this entire thing, the entire range again. Well that doesn't work.
- 03:57 But what if I go back and actually nest a subtotal function in here.
- 04:02 With 9 comma
- 04:05 and we'll go from
- 04:06 E4 through E8 close the brackets, watch that subtotal in row 18
- 04:11 it immediately drops down.
- 04:13 Remember this is the same shape with five rows so if I copy this formula here to here
- 04:19 it is now 4919 which just happens to be the correct answer.
- 04:23 So what SUBTOTAL does is even though this is actually highlighting the
- 04:27 entire range, what SUBTOTAL does is it only sums up numbers that
- 04:31 are used in a later calculation. So it's going to pick up the subtotals but it won't pick up the things that fed into them which is kind of nice.
- 04:39 So that can work out well for us but you have to use subtotals all the way through.
- 04:43 The last thing I want to look at is what happens if we want to round this off? The way we do that if we say =ROUND.
- 04:49 We are going to SUM
- 04:51 our two values comma
- 04:54 F9,F17 close and we will SUM it or ROUND it off to comma. How many decimal places?
- 04:59 Zero so when nesting a SUM function inside a ROUND function
- 05:03 close that bracket and this one will round off to $4639 even though it should end off in 0.5 too.
- 05:11 So it rounds it up. So that's some basic mathematical functions in Excel.
Lesson notes are only available for subscribers.