- 720p
- 540p
- 360p
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
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 Functions22.8 KB Math Functions - Completed
24 KB
Quick reference
Math Functions
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.
Hints & tips
In addition to the ones covered here, there are many other math functions available in Excel.
Login to download- 00:04 Let's look at some basic math functions now.
- 00:07 We've got a couple of listing for products over five week periods.
- 00:11 Now, these two sets of data are relatively the same shape,
- 00:15 which is gonna make some of this a little bit easy.
- 00:17 What we're gonna do,
- 00:18 is we're gonna start by summarizing all of the first week's products here.
- 00:22 And to do that, of course, we'll use the SUM function.
- 00:24 So I'll type in =SUM, Tab, and we'll go and we'll grab this entire range of data,
- 00:31 from B6 to F6, close the parenthesis, and hit Enter.
- 00:36 And we'll now take this and we'll roll it all the way down into the five cells here.
- 00:41 And then, of course, because the next table is the same shape,
- 00:45 we can copy this, and we can paste it right here as well.
- 00:49 And at this point, we've now got some good totals that are moving across that
- 00:52 are nice and consistent.
- 00:54 I'm also gonna come across over here and
- 00:57 I'm gonna sum the data that's above it as well.
- 00:59 So we'll say, SUM, tab and we'll grab the mouse and
- 01:03 select this range, close the parenthesis and hit Enter.
- 01:08 We'll drag those formulas across and
- 01:11 then again because the data is exactly the same shape, it's five rows in both cases.
- 01:15 We can go copy it and paste it here.
- 01:19 So that's nice and easy.
- 01:20 We've got a pretty consistent setup at this point.
- 01:22 But what I really wanna focus on is actually the totals section and
- 01:25 let's see what happens here.
- 01:27 I'm gonna select my cell down in B20, and I'm gonna press the Escape key to
- 01:32 get rid of the marching ants that are surrounding the B11 through G11 range.
- 01:37 Now, I have a few different ways that I could add these things up.
- 01:40 The first thing I could do is I could say equals and I could grab B11 and
- 01:46 add B19, and say Enter, and that'll work just fine.
- 01:53 I can get the same result by doing something similar.
- 01:56 I can say equals SUM, tab, and
- 02:01 then we can grab C11, and C19.
- 02:06 Again, the functionality of what you just saw with the plus versus the sum of 2
- 02:10 individual components, it works out the same.
- 02:14 This is just a stylistic thing, I personally like the SUM function because
- 02:17 it shows intent, which is why I actually use it.
- 02:20 And I can say Enter, and what you can see is that we get 5,443.
- 02:25 And if I were to grab these two components right here,
- 02:28 in my status bar, you can see that it's totaling up nicely.
- 02:31 So I could very easily go and drag these guys across and
- 02:35 everything would work great.
- 02:37 Now, I wanna show you something else though.
- 02:40 What if I were to grab something and say =SUM.
- 02:43 And I were to sum up this entire range, what would happen?
- 02:48 We hit Enter.
- 02:50 We'll takes us to $9,030, and if I press Ctrl+Z,
- 02:53 we can see that that obviously isn't the same as what we had.
- 02:56 As a matter of fact, it was exactly double, why?
- 02:59 Well, because it was summing the entire range including these totals that had
- 03:03 actually been summed below.
- 03:06 Now, I wanna show you another function that's kind of interesting and
- 03:09 it works slightly differently way.
- 03:11 This one here is called SUBTOTAL.
- 03:14 So I'm gonna arrow down and I'm gonna grab subtotal and tab.
- 03:18 And the first thing it does, it asks me for a function name and
- 03:20 you'll see that there is averages, and counts, all kinds of stuff.
- 03:24 The one that I actually want is this one here, number 9 for sum, so
- 03:27 I can click on it, double-click on it, or actually take it.
- 03:31 And then it says, where is the range that you want to subtotal?
- 03:35 I'm gonna say, you know what, let's grab this entire range of data to E5 to E19,
- 03:41 close the parentheses, and hit Enter.
- 03:44 And you'll notice that it gives me $9,800,
- 03:46 which obviously is more than the 2 subtotals that I had.
- 03:50 Again, it's double.
- 03:51 So you go well, but you just did that with a SUM function,
- 03:53 why would you do it with subtotal?
- 03:55 Well, I'm gonna show you.
- 03:56 There's something really interesting about this particular function.
- 03:59 If we go with SUBTOTAL, tab, I'm going to type in 9 for
- 04:04 sum, and I'm gonna sum the data it goes from this area here.
- 04:11 And what I want you to watch is when I hit Enter on this,
- 04:14 I want you to watch what's gonna happen down in cell E20.
- 04:17 If I go and hit Enter, you'll notice that it drops to 77.29.
- 04:21 Now it's still obviously not right.
- 04:24 But if I replace this function, the SUBTOTAL 9 for a sum,
- 04:29 and I grab this range close the parenthesis and hit Enter,
- 04:34 you'll now notice this two values sum to 4,919.
- 04:38 Now this obviously did not happen with the SUM function in place because it just
- 04:43 summed everything, but SUBTOTAL doesn't include previous subtotals.
- 04:47 Why is that important?
- 04:49 It's important because of this, when we look at this subtotal, the range
- 04:54 is from where I have this D here, but it doesn't pick up the cell above.
- 04:58 This subtotal is picking up this area, so
- 05:00 it's also not picking up this particular cell.
- 05:03 But this subtotal is, because it picks up this entire range.
- 05:07 So here's what's interesting, is if I go and type $100 in here,
- 05:12 it picks up in the total even though it's outside the subtotal ranges.
- 05:14 So if it's picked up in a subtotal, it doesn't get added again, but
- 05:18 if it isn't, it'll get picked up, which is kinda nice.
- 05:22 One more function I wanna focus on, I'd like to show you about rounding inputs.
- 05:25 When I see a lot of people do is they'll just drop decimals off things.
- 05:28 But if you look, and you copy and paste, this still has those decimals on it, so
- 05:32 that's not a good thing to do.
- 05:34 If you wanna round a number off, we can actually use a special function for
- 05:38 this called ROUND.
- 05:40 And what ROUND does, I'm gonna hit tab right now,
- 05:43 you'll notice it puts in my parenthesis.
- 05:45 It says, what's the number you want to round and
- 05:47 then comma, how many digits would you like to round it to?
- 05:50 I'm gonna round this to zero decimals.
- 05:52 And at this point, what you'll see is that not it gives me a zero decimal number,
- 05:57 which is rounded off correctly.
- 05:59 So that's the rounding function, and the SUM function and
- 06:01 the SUBTOTAL all working in different ways for some basic math.
Lesson notes are only available for subscribers.