Locked lesson.

Upgrade

About this lesson

Illustration and issues with the SUMIFS function.

Exercise files

Download this lesson’s related exercise files.

SUMIFS.xlsx
9.8 KB SUMIFS - Solution.xlsx
9.8 KB

Quick reference

SUMIFS

Discover how to use SUMIFS function in a formula.

When to use

SUMIFS is similar to the SUMIF function. The key difference is that the range to be conditionally summed, Sum_range, is the first argument of the function rather than the last. This is so there is never any confusion regarding what is to be totaled.

Instructions

Overview

  • Adds the cells specified by given criteria
  • Syntax: SUMIFS(sum_range, criteria_range1,criteria1,…)
  • sum_range are the actual cells to add if their corresponding cells in range match criteria
  • criteria_range1 is the range of cells that you want evaluated by criteria1
  • criteria1 is the criterion in the form of a number, expression, or text that defines which cells will be added

Example

  • sum_range are the actual cells to add if their corresponding cells in range match criteria (F5:F16)
  • criteria_range1 is the range of cells that you want evaluated by criteria1 (B5:B16)
  • criteria1 is the criterion in the form of a number, expression, or text that defines which cells will be added (J4)
  • Finish the formula by selecting the criteria_range2, criteria_range3 and criteria_range4 based in the criteria in cells J5-J7
  • So, to find the Jo’s sales for Business Unit A in the North region with tax, with in the above example, you can use the formula =SUMIFS(F5:F16,B5:B16,J4,C5:C16,J5,D5:D16,J6,E5:E16,J7) (which is $500)
Login to download
  • 00:04 The next cab off the rank is SUMIFS.
  • 00:09 Now, the S is important here, as it's kind of like the multiple of SUMIF.
  • 00:13 So you may recall from the last time out, that this SUMIF function evaluates
  • 00:18 all the date in a range, where a certain criteria on, is met.
  • 00:23 And I'm pronouncing criteria on a very strange way,
  • 00:26 because this one will actually evaluated criteria.
  • 00:30 I'm also represent that in a very strange way, arent I?
  • 00:33 So, this is when you want to have multiple conditions as some of the range
  • 00:36 accordingly.
  • 00:37 Now, if the actual syntax goes range,
  • 00:41 the criteria for that range, sum_range.
  • 00:46 Now, because you can have multiple criteria,
  • 00:48 you bring the sum_range to the front so excel knows that that's the sum_range.
  • 00:53 Then you do criteria_range1 and
  • 00:55 the criteria then criteria_range2 and then the criteria and so on and so forth.
  • 00:59 That's all there is to it.
  • 01:01 Let's go have a play.
  • 01:03 Here's a SUMIFS example then.
  • 01:06 So our table from last time has been expanded.
  • 01:09 But tell me, if I've gotten the business unit now,
  • 01:11 I've also got the sales person, region, and tax, and
  • 01:15 I've created more drop down boxes where I can choose which business unit I want,
  • 01:21 who the sales person is, what region I'm in, and the tax.
  • 01:28 What I need to do this time is not use the sumif function but
  • 01:32 use the sumif ifs function.
  • 01:33 So you go to sumifs open back and
  • 01:36 the first thing it wants is the range that you're going to sum up.
  • 01:40 So it's got to be sales so it's the other way around.
  • 01:42 F5-F16 first of all.
  • 01:45 Then it wants the first criterion range.
  • 01:48 I can't bring myself to say criterion range.
  • 01:50 What first criterion range which is going to be to assess the business unit.
  • 01:54 So that's going to be column b and the condition is going to be, so
  • 01:58 it has to be A.
  • 01:59 Then it's the sales person.
  • 02:02 Comma. Here's the sales person.
  • 02:04 Comma.
  • 02:05 The region.
  • 02:07 Comma.
  • 02:08 North.
  • 02:09 Comma.
  • 02:10 Tax, yes or no.
  • 02:13 Comma. Unfortunately, the tax is yes.
  • 02:17 Answer 500, so A Jo North Yes?
  • 02:23 There's one A Jo North Yes, and there's another A Jo North Yes, so there are two.
  • 02:28 Add those two together, 100 plus 400 is 500.
  • 02:31 Nice and straight forward.
  • 02:35 Now, notice that the actual conditions are not case sensitive.
  • 02:40 So if I were to go here, change this to NortH,
  • 02:44 capital H instead, it would still be 500.
  • 02:49 It's not particular just watch out.
  • 02:51 Whereas I put north and a space, that wouldn't work.
  • 02:56 So just be careful of those.
  • 02:59 Another thing is wildcards are permitted.
  • 03:01 So in here, instead of having A, let's say I wanted to ignore that and
  • 03:04 just have anything.
  • 03:06 Specified J4, I could actually remove that.
  • 03:09 And in speech box, put an asterisk instead.
  • 03:13 And that means anything so it's just a case of putting a wildcard in here.
  • 03:18 And that makes it 1400, because then I've got Jo, North,
  • 03:21 Yes there, Jo, North, Yes there, and Jo, North,
  • 03:27 Yes there which equals 900 plus 400 plus 100 is 1,400, which is what this is doing.
  • 03:32 Now that could have been just so
  • 03:35 as easily done by actually removing this conditional all together.
  • 03:40 I just wanted to show you these wild cards in here.
  • 03:43 Still 1,400.
  • 03:45 That's a pretty comprehensive SUMIF's example and
  • 03:48 it leads us right into the next one which is sum product.

Lesson notes are only available for subscribers.

Gift this course