Locked lesson.

Upgrade

About this lesson

Illustration and issues with the SUMIF function.

Exercise files

Download this lesson’s related exercise files.

SUMIF.xlsx
11.8 KB SUMIF - Solution.xlsx
11.8 KB

Quick reference

SUMIF

Discover how to use SUMIF function in a formula.

When to use

SUMIF combines SUM with IF to provide conditional summing, i.e. where you wish to add numerical values provided they meet a certain criterion.

Instructions

Overview

  • Adds the cells specified by a given criterion
  • Syntax: SUMIF(range,criterion,sum_range)
    • range is the range of cells that you want evaluated by criterion
    • criterion is the single criterion in the form of a number, expression, or text that defines which cells will be added
    • sum_range are the actual cells to add if their corresponding cells in range match criterion. If sum_range is omitted, the cells in range are both evaluated by criterion and added if they match criterion.

Example

  • The function SUMIF(Range,Criterion,Sum_range) is ideal for summing data based on one requirement:
    • Range is the array that you wanted evaluated by the criterion (in this instance, cells B5:B16)
    • Criterion is the criterion in the form of a number, expression, or text that defines which cell(s) will be added, e.g. G4
    • Sum_range are the actual cells to be added if their corresponding cells in Range match the Criterion e.g. C5:C16
  • So, to find the sales for Business Unit B in the above example, you can use the formula =SUMIF(B5:B16,G4,C5:C16) (which is $1,800)
Login to download
  • 00:04 So if you put if and sum together, what do you get?
  • 00:08 That's right, IFSUM.
  • 00:09 But if you put them the other way around, you get SUMIF.
  • 00:14 SUMIF does exactly what you think it does,
  • 00:17 it sums values if they meet a certain criterion.
  • 00:21 Now I'm saying criterion in a very strange way.
  • 00:24 Not because I've got an even weirder accent than normal, but
  • 00:28 because of the fact that it is a single criterion is how SUMIF works.
  • 00:33 So you actually have a range that you are going to evaluate the criterion for.
  • 00:37 What that condition is?
  • 00:38 And then the range you want to sum as a result,
  • 00:41 where it will actually sum the corresponding value.
  • 00:45 If you don't specify some range,
  • 00:48 It will actually assume it's the range you're looking to do.
  • 00:51 It can be used to have conditions where things can link to actual cells,
  • 00:55 although that requires concatenation.
  • 00:59 More on that in a second, when we look at the Excel example.
  • 01:02 It won't see closest data if there's no match, there's no match, the end.
  • 01:05 But it's not case sensitive.
  • 01:08 Many people assume that you can only use SUMIF on a row or a column.
  • 01:12 Actually it can work on an entire range of rows and columns.
  • 01:16 It just requires the range and sum range to be identical.
  • 01:19 Sum range should be specified clearly, Excel does try to guess it.
  • 01:24 If you don't specify it properly, it will ignore you and
  • 01:28 put down what it should be, but it will eat more memory as a result.
  • 01:33 If you have lots of SUMIF's though it can start to slow down Excel.
  • 01:37 And pretty much sum invention is the same one that's used for PivotTables.
  • 01:41 In summary let's take a look at some Excel examples.
  • 01:46 In my first SUMIF example, I have got some sales data by business unit.
  • 01:53 Here it is in this little table here.
  • 01:56 I've got four business units, A, B, C and D and their corresponding sales.
  • 02:01 What I want to do, is work out what the total sales were for
  • 02:04 a particular business unit.
  • 02:06 In cell G4 I have created a data validation drop down list.
  • 02:10 I showed you how to do that in an earlier session.
  • 02:13 I'm going to select A.
  • 02:15 I want to know what the total sales were for business unit A.
  • 02:20 To do this, I use the SUMIF function.
  • 02:23 Make a pretty silly SUMIF example if I wasn't going to use that, now wouldn't it?
  • 02:28 Now the first thing I need to do is create the range.
  • 02:31 What is the the range?
  • 02:32 And the range is whatever I'm basing criterion on.
  • 02:35 With that in mind, I'm looking for the A's.
  • 02:37 So I'm going to highlight this range here from B5 to B16 and the criterion Is this.
  • 02:45 Now look, it actually says criteria here.
  • 02:49 Microsoft, the great programmers,
  • 02:52 they produce great spreadsheets but English isn't necessarily their forte.
  • 02:56 Okay, it criterion.
  • 02:57 So, I need one criterion we can put in here.
  • 03:00 And then the sum range.
  • 03:01 What we want to sum up, the corresponding route values, is in column C, C5 to C16.
  • 03:06 So what that's saying is, look in column B for any A's.
  • 03:11 And wherever you find an A, sum the corresponding value from column C.
  • 03:14 The answer, 1,000.
  • 03:18 Here's my four A's, the corresponding values are here.
  • 03:22 That's of 2000. If I change this to a B, here's my B's.
  • 03:27 The corresponding values add up to 1800 here.
  • 03:33 That's it, that's pretty much how SUMIF works in a nutshell, or
  • 03:37 even on a spreadsheet boom, boom.
  • 03:39 And there's an alternative way of creating a formula
  • 03:43 when you only used two rather three arguments.
  • 03:47 Let me give you an example by saying, I want to find out what
  • 03:52 the total sales were with sales recorded at over $450 I say.
  • 03:57 Here I would say equal SUMIF, the criteria this time is this column,
  • 04:02 these sales here.
  • 04:04 And I need it to be greater than 450.
  • 04:06 Now, I can put into speech box greater than 450 like this.
  • 04:12 And then I need to put the sum range.
  • 04:13 Well the sum range would be the C5 to C16 again.
  • 04:18 So I don't bother.
  • 04:20 That's why in here do you see some ranges in square brackets.
  • 04:23 That means it's optional.
  • 04:25 It doesn't mean put square brackets around it.
  • 04:27 It means you don't always have to put it in.
  • 04:29 If I do that, I get 6,800.
  • 04:32 These are all of them that are greater than 450.
  • 04:35 6,800, piece of cake.
  • 04:38 Problem is, I've actually put in here hard code greater than 450.
  • 04:45 I wanted to actually reference this cell, so
  • 04:48 I'm going to do greater than that cell there, G8, 0.
  • 04:54 That's because text it doesn't evaluate, doesn't know what that means, so
  • 04:58 it ignore it.
  • 04:59 So, what I have to do is put it outside of the speech marks and I have
  • 05:03 to sellotape it to the great them and sellotape command is the ampersand symbol.
  • 05:09 That's the concatenate operator and what it does,
  • 05:13 is join the great than to this G8.
  • 05:17 If I do it that way, it works.
  • 05:20 And that's how you create a dynamic criteria, and that's what
  • 05:24 we mean by dynamic criteria, where you can change it by changing the cell.
  • 05:28 Not too difficult.
  • 05:31 In the next example,
  • 05:32 I wanted to show you that it doesn't have to just be on a column or a row.
  • 05:36 When something has just one column or one row, it's known in technical terms,
  • 05:40 as a vector.
  • 05:42 When it has more than one row, and more than one column, this is an array.
  • 05:46 So you can see in this particular case, we have got a table, or
  • 05:49 an array, of business unit data and corresponding sales.
  • 05:54 SUMIF still works exactly the same.
  • 05:55 Let's say I want to have business unit A, so I want to evaluate all of these.
  • 06:01 So I can say equals SUMIF open bracket, highlight this range,
  • 06:06 comma look for that comma, give me the corresponding value in this range.
  • 06:12 3,100 and if you add up those green cells, you will see it works.
  • 06:18 Change it to B, again it will work.
  • 06:21 You can have a plan.
  • 06:23 The final example is that be aware again.
  • 06:28 In this one I've got a row better office data, and
  • 06:31 here I have got cells data going in a column.
  • 06:35 So this one relates to that, that one relates to that,
  • 06:38 that one relates to that and so on.
  • 06:41 My business unit here, A.
  • 06:44 Let's use a SUMIF, equals SUMIF,
  • 06:49 open bracket, this range, F8, this column.
  • 06:56 Answer, 100?
  • 07:01 Now, those four here correspond to those.
  • 07:04 It doesn't work.
  • 07:06 And that's because when you have
  • 07:09 two ranges that are not of the same dimensions, SUMIF ignores you.
  • 07:13 It takes the top left-hand cell B7, but
  • 07:16 then what it's doing is It's actually making it the same dimension, so
  • 07:21 it's going 12 across this way so it's actually evaluating this range.
  • 07:25 It ignores the rest of it.
  • 07:27 SUMIF is a self correcting function in Excel, be careful.
  • 07:31 And when it doesn't correspond to your range,
  • 07:34 so even if I'd actually got it to almost right.
  • 07:37 If I'd actually done this instead, B7 to there, not quite right,
  • 07:41 it should go to column M, it would still have the same problem.
  • 07:46 It will calculate what sort of we call a volatile function.
  • 07:48 Every time you press enter, F9,
  • 07:49 whatever it will recalculate, and this can eat memory, so just be careful.
  • 07:55 SUMIF, pretty much on the plate for you.

Lesson notes are only available for subscribers.

Gift this course