Locked lesson.
About this lesson
Learn to use SUMIF, COUNTIF, and SUMIFS to add cells only when certain conditions are met.
Exercise files
Download this lesson’s related exercise files.
Conditional Math.xlsx18.7 KB Conditional Math - Complete.xlsx.xlsx
26.1 KB
Quick reference
Conditional Math
Using SUMIF, COUNTIF, and SUMPRODUCT 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, a 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/COUNTIFS/AVERAGEIFS
- Allows calculations where multiple conditions are met
- Unlike the “IF” versions of the formula, the “IFS” versions require the calculation range first
- After the calculation range, one or more pairs of parameters are provided, where the first is the range to check, and the second is the condition
- In order to be included in the total, ALL of the conditions must be true for any given line
- Keep in mind that all calculation and criteria ranges must have the same number of rows
- 00:04 This example is based on a list of Vancouver Canucks hockey players from
- 00:08 several years ago with their jerseys, the number of years remaining on their
- 00:12 contract, and what their salary cap level was that was contributed to the team.
- 00:16 And I want to run some basic statistics on this where I can count and
- 00:20 sum up some of the different pieces from this to figure out how they contribute to
- 00:25 the overall team.
- 00:26 Now, this is going to involve both counting and summing.
- 00:28 It's also going to involve conditional logic, like IF statements.
- 00:32 But the good thing is we have functions that are specifically designed to do both
- 00:36 of those things without actually having to write an IF function and
- 00:39 a separate COUNT or SUM.
- 00:40 So let's take a look at this.
- 00:41 We're going to start with COUNTIF.
- 00:44 And what I'm going to do is I'm going to count the number of records where
- 00:47 the jersey number is equal to 17.
- 00:49 And we can see that there's only one of those.
- 00:51 But here's how this works.
- 00:52 We'll grab the column for our jersey number that we're looking for, and
- 00:57 we'll set the criteria to be equal to 17, the value from G7.
- 01:00 We're going to close the parenthesis and hit Enter.
- 01:03 And as you'd expect, we get one, which is Ryan Kesler's jersey.
- 01:07 Fair enough.
- 01:08 Let's see if we can count the number of jerseys that are less than or equal to 5.
- 01:12 We're going to go and still use a COUNTIF function.
- 01:16 We're going to target the column that has those values, and
- 01:21 what we want is items that are less than or equal to 5.
- 01:25 Close the parenthesis, so all seems logical so far.
- 01:28 I'm going to hit Enter, and I get an error.
- 01:31 Now, the reason for this is because this actually needs to be passed a little bit
- 01:35 differently into this parameter.
- 01:37 We actually have to wrap the less than or equal criteria in quotes and
- 01:42 join it using our concatenation function, text function, the and symbol.
- 01:47 This will join it to 5, so
- 01:49 that what actually gets passed to this is less than or equal to 5.
- 01:52 And when we hit Enter, we get three records,
- 01:55 which represent Christian Ehrhoff, Kevin Bieksa, and Roberto Luongo.
- 02:01 All right, now what if I want to figure out how many years
- 02:05 are remaining on jersey number 14's contract?
- 02:09 Well, in this one here, we actually have the number of years remaining.
- 02:12 I'm not going to use a COUNT, what I'm going to use is a SUMIF.
- 02:15 So we're going to say =SUMIF,
- 02:17 and you'll notice that this one's got a few more criteria here.
- 02:20 Well, COUNTIF only have range and criteria.
- 02:22 This one's also got an optional sum_range, so let's take a look at this.
- 02:25 We're going to take the number of actually, sorry, no, we're not.
- 02:28 We're going to grab the jersey number.
- 02:29 That's the important part that we want to start with here,
- 02:32 because that's the criteria we're looking for.
- 02:34 We know it's equals, so we're going to make it jersey number 14.
- 02:38 If I go and just close the parentheses on this without putting in an optional piece,
- 02:43 we get back 14, why?
- 02:44 Well, we're looking through the jersey number, we find 14, and we sum it.
- 02:49 But that's not what we're looking for.
- 02:50 We're looking for the number of years remaining.
- 02:52 I need 3.
- 02:55 So here's what's going to happen.
- 02:56 I'm going to go back here and I'm going to put in this alternate sum_range, and
- 03:01 that's going to be from the Years Remaining column.
- 03:04 Here we go, we hit Enter, and we now have 3 years remaining.
- 03:08 Let's try this for jersey numbers greater than or equal to 14 and
- 03:12 get a total count on this one.
- 03:14 So we'll go SUMIF.
- 03:16 We're going to take the range of jersey numbers.
- 03:19 Like we did with our COUNTIF, this one also needs to be passed as text ,so
- 03:24 we're going to go with greater than or equal to and Jersey number 14.
- 03:29 And this time, again, we're going to put in the sum of years remaining.
- 03:33 Close the parenthesis, and hit Enter, and we have 13 total years of contract
- 03:38 remaining on these players that have jersey numbers higher than 14.
- 03:42 So if we take a look, we've got these guys here and equal to, there we go.
- 03:47 That is 13 if you add all them together, fantastic.
- 03:51 Let's go for one that's slightly trickier.
- 03:53 This one has three criteria.
- 03:57 COUNTIF and SUMIF are great when we have a single criteria.
- 04:00 But what if you've got a whole bunch of stuff that you want to fit into this?
- 04:03 Well, this is where we actually use an alternate version of this function.
- 04:07 So we're going to go again for the Total Cap hit, but this time, instead of using
- 04:13 SUMIF, we're going to use SUMIFS, and this one allows us multiple sum criteria.
- 04:19 What's confusing about this function, though, is that where the other ones
- 04:23 put the sum range at the end, this one actually says, what column would you like
- 04:28 to sum first and then tell me about the criteria number you're looking for.
- 04:33 So what's criteria one?
- 04:35 Well, it's going to be jersey number.
- 04:38 What is the criteria we're looking for?
- 04:40 We're going for less than or equal to and 23.
- 04:46 We can then go into an optional additional criteria.
- 04:49 This is going to be for jersey number again.
- 04:53 We'll put in the criteria we're looking for here,
- 04:58 which is greater than or equal to, and 14.
- 05:02 And now, I'm going to put comma, and
- 05:04 you'll see that we're going to go into a final criteria.
- 05:06 And this time, we're going to look for years remaining.
- 05:09 So grab this column here, and for this one,
- 05:13 we're going to go with less than or equal to and our 3.
- 05:18 We'll now close the parenthesis.
- 05:19 You can see we're feeding three criteria into this.
- 05:22 And when we hit Enter, we come back with 11,350,000 is
- 05:26 the Total Cap hit that we actually have committed there in this case.
Lesson notes are only available for subscribers.