Conditional Math

Quick reference

• 00:04 In this example, we're gonna look at conditional math.
• 00:07 Now this is where we want to apply a mathematical function
• 00:11 if certain criteria are actually met.
• 00:13 And we have a very specific function for this one called SUMIF.
• 00:19 Now the way SUMIF works is we provide it a range that we want to look at.
• 00:24 So in this case, if I wanna go and figure out what the cap hit is for
• 00:29 players where the jersey number equals 14, the first thing I'm gonna do is say,
• 00:36 let's look at the jersey number and see if it equals 14.
• 00:41 Then if it does, we're gonna go and get the appropriate row from this range here.
• 00:47 So one of the key things that we wanna make sure is that these two ranges,
• 00:50 the range and the sum range are the same height.
• 00:54 Now when we close our parenthesis here,
• 00:56 what you'll see is it's gonna give us an answer of $2 million.
• 01:00 And the reason being is because we're looking to say, I want to see the cap hit
• 01:04 where the jersey equals 14 which would give us Alexander Burrows at 2 million.
• 01:12 What if I wanted to go and
• 01:13 sum things though where the Jersey number was greater than or equal to 14.
• 01:17 Well this involves a little bit more work.
• 01:20 We're still gonna go with the SUMIF function.
• 01:23 So we're gonna grab SUMIF.
• 01:25 And we're still gonna look in the range of jersey numbers.
• 01:29 However, the secret with the SUMIF is that the second parameter's
• 01:33 actually text-based.
• 01:36 So what we need to do is say, quote, greater than or
• 01:39 equal to, quote, and the value of 14.
• 01:44 And It's gonna put all that together, say greater than or equal to 14,
• 01:48 at which point we can now say comma, and grab our sum range for our cap hit.
• 01:54 Close our parenthesis and say OK, and it gives us 22.4 million.
• 01:59 And if we go back and we look at all of the values where the jersey number is
• 02:02 greater than or equal to 14,
• 02:04 we can see that indeed down at the bottom here it actually sums up to 22.4 million.
• 02:10 What if we want to do a count?
• 02:13 Well for that we have a similar function which is called =COUNTIF,
• 02:18 you can see it right down here.
• 02:21 With the COUNTIF, what we're gonna do is we're gonna say well, let's go and
• 02:24 see our jersey number here.
• 02:27 What we'd like is we'd like to have it,
• 02:31 wear it, quote, is greater than, and, 17.
• 02:36 So the only thing that you have to really worry about here is if you're working with
• 02:40 equals you don't need to put anything between quotes and join it to your text.
• 02:43 But if you're not, well at that point, then you do have to be concerned about it.
• 02:48 Now the COUNTIF is just gonna count these values.
• 02:50 So we don't have to assign it an alternate range.
• 02:52 And now when we hit Enter it tells that there's three players that are actually
• 02:56 with a jersey number greater than 17.
• 02:58 What if we want less than or equal to 5?
• 03:01 Well we're gonna go and we're gonna say again, COUNTIF.
• 03:04 We'll go and we'll pick up our range and we'll say,
• 03:09 comma, quote, less than or equal to, quote,
• 03:13 end, and then we'll pick up our value of 5.
• 03:17 We'll close our parenthesis and hit Enter, and just like that we've got 3.
• 03:22 Of course, if I wanted to have it so that it was only counting jerseys where they're
• 03:26 less than 3 or equal to 3, I could type in that and
• 03:28 you'll notice that we dropped the two values because only Kevin Bieksa and
• 03:31 Roberto Luongo actually have jersey numbers that are three or less.
• 03:36 I'm gonna undo that and set it back to 5, and
• 03:38 I wanna look at our final one because this one starts to get much more complicated.
• 03:44 In this case, I have a lot of different criteria that are actually driving
• 03:47 whether or not I want to actually include these values into the cap hit area.
• 03:52 I wanna know if the jersey number's less than or equal to 23,
• 03:55 and if the jersey number is greater than or equal to 14, and
• 03:57 the years remaining in the contract is less than or equal to 3.
• 04:01 There's a lot of different things here.
• 04:03 When we have multiple criteria that we're working with, we actually need to reach to
• 04:07 a slightly different function called SUMIFS, plural.
• 04:13 Now in this one, oddly,
• 04:15 it actually works in the opposite way than what we saw with the SUMIF.
• 04:19 In the SUMIF, we say where's the range and what's the criteria?
• 04:22 And then at the end we provide the sum range.
• 04:24 In a SUMIF statement, the sum range comes first.
• 04:29 Next, what we're gonna do is we're gonna look for the criteria range one.
• 04:33 Now this is gonna be the jersey number.
• 04:36 Following that is what is the criteria.
• 04:39 So now we're gonna say, is this less than or equal to, &23.
• 04:45 We then optionally have the ability to assign another criteria,
• 04:49 which again is actually going to be the jersey number.
• 04:53 Because this time we also want to know if it's greater than or equal to &14.
• 05:02 And finally, we're gonna put it in a third criteria,
• 05:05 which is the number of years remaining in the contract.
• 05:09 And we're gonna check if those are less than or equal to, &3.
• 05:16 And if we wanted to do more we could continue, but we don't need to,
• 05:19 that's all of our criteria for now.
• 05:21 So we would close the parenthesis and we're gonna go and
• 05:24 hit Enter on this mega formula.
• 05:26 And notice that it works, it come out to $11.3 million and if you added through,
• 05:29 you're gonna notice that it works out just perfectly.
• 05:33 And again, it's completely dynamic, so if we wanted to go back and
• 05:36 say just give me people where the years remaining is 5,
• 05:39 it'll come to 16.3 million, just like that.

Lesson notes are only available for subscribers.