Locked lesson.

Upgrade

About this lesson

Illustration and issues with the SUM function.

Exercise files

Download this lesson’s related exercise files.

SUM.xlsx
9.2 KB SUM - Solution.xlsx
9.7 KB

Quick reference

SUM

Discover how to use SUM function in a formula.

When to use

SUM adds things up. It may include cells, numbers or ranges. In the context of financial modelling, summations are usually of numbers either directly above or to the left of the cell in question.

Instructions

Overview

  • Adds all of the numbers in a range of cells
  • Will only evaluate numbers and adds the numbers together to return their addition. It is not quite the same as simply using the "plus sign" (arithmetic operator) between each number within the SUM function
  • Need to be careful if downloading data from elsewhere
  • Data may not be what it seems
  • Check integrity, properties and ‘cleanliness’ of data

Example

  • In this example, The values in cells E3:E7 have been totaled in two distinct ways: the first uses the aforementioned SUM function with ALT + =, the other has added each cell individually using the ‘+’ operator
  • Cell E5 has been modified. It has been stored as text, even though it looks like the number 3. SUM treats this as having zero value whereas the more convoluted addition carries on regardless.
Login to download
  • 00:01 The SUM function, it's a rather simple one and
  • 00:06 what could I possibly tell you about this that you don't already know?
  • 00:14 It does exactly what it says on the.
  • 00:18 It adds up all of the numbers in a range of cells.
  • 00:21 It's simply the shorthand equivalent of putting a plus sign between
  • 00:26 everything you want to add up.
  • 00:28 You may have noticed the last graphic, I've repeated it in this slide.
  • 00:32 1 plus 2 plus 3 plus 4 plus 5 isn't 12, it's 15.
  • 00:39 And one of the things you gotta be careful of when using the xl
  • 00:43 sum function is keeping your data clean.
  • 00:46 Let me give you an example.
  • 00:48 Let's take a look at some examples.
  • 00:52 Get it some examples.
  • 00:55 I know I shouldn't give up the day job.
  • 00:58 I have two SUM examples on this page.
  • 01:00 I've got the numbers 1, 2, 3, 4, 5 twice and I want to add them up.
  • 01:04 It's pretty straightforward.
  • 01:06 I can just go here to cell H4 and type in =sum, open brackets,
  • 01:11 highlight these numbers here, close brackets, press enter, voila!
  • 01:16 Sum C4 to G4.
  • 01:19 Ain't nobody got time for that.
  • 01:21 That's far too subtle.
  • 01:23 If the numbers are directly to the left of what you're summing, be there, and
  • 01:28 instead, hold the ALT button down and press equals.
  • 01:34 Do it for you, simple.
  • 01:37 It doesn't just work for numbers to the left.
  • 01:41 Take this one.
  • 01:42 Here I am now in cell C12, wanting to sum the values in cell C7, to see 11.
  • 01:48 What I can do here alt equals, sum from above, as well voila!
  • 01:55 Easy-peasy.
  • 01:57 Let's have a look at the next example.
  • 02:00 Very straight forward.
  • 02:02 Let me do it the long handed way equals sum.
  • 02:07 Why are we doing this?
  • 02:09 15, 12, now what do we do about plus?
  • 02:17 Okay, now who would do it this way?
  • 02:21 That, plus that, plus that, plus that.
  • 02:23 Yep, wanted to kill some time in this presentation.
  • 02:29 Interesting, you might think someone plus to the same thing.
  • 02:32 Here you go, here's an example where they don't.
  • 02:35 What on Earth is going on?
  • 02:37 Well, here's the clue.
  • 02:39 I deliberately type this in long-handed.
  • 02:41 Watch what happens if I do alt equals again.
  • 02:47 It's only summing those 7 and 8, because it only sums numbers.
  • 02:53 What I've done is, in cell C6,
  • 02:55 do you see I've put an apostrophe before the number 3?
  • 03:00 What that does is treat the numbers if it's stored as text.
  • 03:04 Now I've been a bit crafty here.
  • 03:06 If I go to File Options in here.
  • 03:09 What I've actually got if I'm plowing through here is the ability
  • 03:15 in formulas to actually enable background error checking.
  • 03:19 Now, by default, that is switched on.
  • 03:22 I've deliberately switched it off.
  • 03:24 And I'll switch it back on, because one of the things in here,
  • 03:28 if you look through this set of details down here, is it checks for
  • 03:33 cells containing formulas that result in error.
  • 03:35 Inconsistencies represented as two digits, and this one is the important one,
  • 03:40 numbers formatted as text or proceeded by an apostrophe.
  • 03:44 There is also other ones here but that will be one of the things if I click OK.
  • 03:48 You see it would it.
  • 03:49 If I click on this cell it puts this exclamation mark, click on it numbers
  • 03:55 text and what that does, is when you sum it,
  • 04:00 it ignores the number three, it treats it as a zero, that's the problem.
  • 04:04 So how am I going to actually get that to work?
  • 04:11 Let's just copy the formula here.
  • 04:14 I'm going to actually take these numbers, and simply multiply them by 1.
  • 04:20 This is how we can clean the data.
  • 04:26 And this is the point the slide was trying to make earlier.
  • 04:30 We've got to be careful when bringing in data from third party systems.
  • 04:34 Matrix Information Systems, other people's spreadsheets, whatever.
  • 04:37 We assume all numbers are always stored as numbers.
  • 04:40 That might not be the case.
  • 04:42 We can often treat functions like sum with contempt.
  • 04:45 I was pretending to here.
  • 04:47 But it's easy to get caught out as I show.
  • 04:50 This is why I've done this particular sessions.
  • 04:52 Because everyone thinks they know sum, but there are some problems inherent.
  • 04:56 So just be careful.

Lesson notes are only available for subscribers.

Gift this course