Locked lesson.

Upgrade

About this lesson

Learn to use SUMIF and COUNTIF to add cells only when certain conditions are met.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Conditional Math.xlsx
22.8 KB Conditional Math - Completed.xlsx
23 KB

Quick reference

Topic

Using SUMIF and COUNTIF to add cells only when certain conditions are met.

When to use

Useful when you need to summarize values only in cases where certain criteria are true.

Instructions

SUMIF

  • Allows provision of a range of values to examine, criteria to meet, and a different range to sum.
  • Each row in the existing range is examined against the criteria.
  • If a condition is met, then the matching row in the “sum range” is added to the total.
  • Mathematical decisions provided as the criteria should be wrapped with quotes.
  • =SUMIF(A1:A10,”>5”,B1:B10) will sum all records in B1:B10 where the corresponding value in A1:A10 is greater than 5.

COUNTIF

  • Allows provision of a range of values to examine, as well as a criteria to meet in order to be counted.
  • Each row in the existing range is examined against the criteria.
  • If a condition is met, then the matching row in the “count range” is added to the total.
  • Mathematical decisions provided as the criteria should be wrapped with quotes.
  • =COUNTIF(A1:A10,”>5”) will return a count of all records in A1:A10 where the value is greater than 5.

SUMIFS

  • Allows provision of a multiple ranges of values to examine, a criteria to meet, and a different range to SUM
  • All tests must be true in order to have the value added in
  • =SUMIFS(B1:B10,A1:A10,”>=2”,A1:A10,”<=5”,) will sum all records in B1:B10 where the corresponding value in A1:A10 is greater than or equal to 2 AND less than or equal to 5.
  • SUMIFS did not exist in Excel 2003 and earlier.  To achieve multiple condition sums in Excel 2003 and prior, you must use the SUMPRODUCT function.
Login to download
  • 00:04 Our next example here focuses on performing conditional math.
  • 00:08 So, this is where we want to perform math but only if certain criteria are met.
  • 00:14 And the way that we do this is that we use a special function called SUMIF.
  • 00:20 And basically what SUMIF will do is it will allow us to specify a range.
  • 00:27 So in this particular case here,
  • 00:28 we can say let me take a look at all of the jersey numbers that we have over here.
  • 00:34 And it says would you like to make a criteria match these things?
  • 00:38 I say, yeah, I'd like to look at where the value,
  • 00:41 the criteria is the jersey number 14.
  • 00:44 So it's gonna look through every one of these records to find where the jersey
  • 00:49 number matches 14, and then it says what range would you like to sum?
  • 00:54 I'll say give me the cap that we hit here.
  • 00:57 And at this point we can go back and hit enter and
  • 01:00 you'll notice that it says if the jersey number is 14, we hit $2 million.
  • 01:05 If I were to change this to jersey number 23, it would return $3.25 million.
  • 01:11 So this is kind of a nice little piece here that we can
  • 01:14 use to conditionally pull a number out of a list of values.
  • 01:17 But what if we wanted to see where the jersey number was greater than or
  • 01:21 equal to 14?
  • 01:22 Well let's give this a go here.
  • 01:24 We'll say =SUMIF, and
  • 01:27 we'll specify the range of jersey numbers that we wanna look in.
  • 01:31 And for the criteria this time, we actually have to feed
  • 01:35 the ">=", in between quotes.
  • 01:41 And then join it to the jersey number.
  • 01:44 This is a little bit of a pain to be honest with you,
  • 01:47 but it's the only way that we can actually do this where we actually want
  • 01:50 to put in multiple conditions in one place.
  • 01:52 Or even if you just wanted to put in greater than, you've got to put in
  • 01:55 greater than between quotes and what you're looking for for the cell reference.
  • 02:00 Then we can say the sum range.
  • 02:02 And we'll put in the salaries.
  • 02:05 Close our parenthesis and say OK.
  • 02:07 And what you'll see is that we get 22.45 million.
  • 02:10 Now, just to check this out and make sure that this actually works correctly,
  • 02:14 Let's make the jersey number be greater than or equal to 23 for a second.
  • 02:19 Because that should give us Alexander Edler's salary, and
  • 02:23 Henrik Sedin's which should be 9.35 million.
  • 02:26 So it definitely appears to be working there.
  • 02:28 I'll set this back to 14.
  • 02:32 What about where we want to count?
  • 02:34 We don't want to sum.
  • 02:35 Well we have the same kind of a function here that we can use which is called
  • 02:39 COUNTIF.
  • 02:41 And COUNTIF basically says give me the range, and give me the criteria.
  • 02:48 Well in this case again, we've got a greater-than.
  • 02:50 So we need to go with ">"&, 17.
  • 02:58 And it will now go and count every jersey number that's greater than 17, and
  • 03:02 there should be three of them, because we've got 22, 33, and 23.
  • 03:08 If we want the jersey number to be less than or equal to 5, we would say COUNTIF.
  • 03:14 We'll select our range, and say let's count where this is less than or equal to,
  • 03:21 and our cell.
  • 03:26 Close our quotes and hit enter, there we go.
  • 03:29 So there's three of those where the jersey number is less than or equal to 5 as well.
  • 03:35 Now, what if we want to go and provide multiple different criteria to this?
  • 03:39 In this case we're using one criteria, we're saying greater than or equal to.
  • 03:42 But what if we want multiple different things?
  • 03:45 This becomes more challenging.
  • 03:47 There is a crazy technique that people used to use in the past called SUMPRODUCT.
  • 03:52 But fortunately, we actually got a new function in Excel 2010 called SUMIFS.
  • 04:01 And this will allow us to open up our sum range.
  • 04:04 It says what's the range you want to sum first?
  • 04:06 Well we'll say you know what, in this case we're going to sum the cap hit.
  • 04:09 And it says great, what's the criteria range?
  • 04:11 Well in this case we want the jersey number to be less than or equal to 23.
  • 04:16 So we're going to say jersey number.
  • 04:18 And it says what is the criteria.
  • 04:20 Again, we're gonna be in quotes, "<="& 23.
  • 04:25 So this makes sense so far.
  • 04:26 And then it says what's your criteria range number two?
  • 04:29 Well, we're also gonna say and the jersey number, here.
  • 04:36 What's the second criteria?
  • 04:38 ">="& 14.
  • 04:43 And then we're also gonna look at the number of years remaining.
  • 04:48 And the criteria we'll use here is
  • 04:53 less than, whoops, "<="& 3.
  • 04:59 It builds a pretty big, long, and ugly formula.
  • 05:03 But you can see that when we hit enter, we do get a value.
  • 05:06 And if you audit it back, you'll see that it's correct,
  • 05:09 allowing us to actually build some strong conditions,
  • 05:12 a lot of different conditions to add and pull out specific numbers.
  • 05:16 So this can be very good for mining information from big, long lists.

Lesson notes are only available for subscribers.

Gift this course