Conditional Math

Quick reference

• 00:03 In this video we're going to look at conditional math where we want to sum or we want to
• 00:08 count some records but only if they meet specific conditions.
• 00:12 So the way that we're going to do that we're going to look at our table here of
• 00:16 customer numbers and we're going to sum up the purchase
• 00:19 where a customer number equals something. So right now we're at =2. So the way that we'll do that is
• 00:24 we'll work with the SUMIF function. So =SUMIF
• 00:29 and it asks us for a range, a criteria and optionally a sum range. So the range is where
• 00:35 are the conditions that you want to look for? So we are going to select A4 through A11.
• 00:40 And then it says the criteria, what would you like, well we'd like it where the customer number equals two and then
• 00:46 what range would we like to sum? We will sum the purchase range from D4 to D11.
• 00:51 When we close the bracket and hit Enter it returns $1188 which as you can see by looking through here there's only
• 00:57 one record where the customer number equals two, the purchases are 1188 so that works out perfectly.
• 01:04 What about where the customer number is greater than or equal to five? We'll reach back to SUMIF again
• 01:09 we'll select our range
• 01:12 and we'll give it the criteria of greater than or equal to five
• 01:17 and provide it the sum range in the purchase area
• 01:21 close the bracket and we'll hit Enter and it comes back with an error as you can see.
• 01:27 The issue here is that Excel doesn't understand the greater than or
• 01:30 equal to five portion. And what it needs around this is
• 01:34 it needs to know that this is not a mathematical operation but rather that it's something you're looking for. So we
• 01:38 have to feed that back as text so if we wrap the greater than or equal to five in quotes
• 01:43 and we hit Enter what we can see is we actually get an answer that works.
• 01:47 And if we go and look and say well this was greater than or equal to five, this one's greater than or equal
• 01:52 five as are these two here we can now see in the bottom that our totals do actually
• 01:56 match up to 6114 as our formula returned. That's good.
• 02:02 What if we wanted to just count customers where they actually met the conditions?
• 02:06 We can sum it and provide it well, no we probably wouldn't
• 02:10 want to do that at all. Let's go into the COUNTIF formula because that's what's really built for this purpose.
• 02:15 So we say =COUNTIF
• 02:17 this will go and it will actually count the numbers for us so in this case we feed it a range,
• 02:23 and then we say what's the criteria that we want and it works the same as SUMIF. We need to put our quotes around this, greater than four.
• 02:31 Close our quotes
• 02:33 and as we close our bracket we hit Enter. We see 4.
• 02:37 So greater than 4, there's 5, there's 6, there's 7 and 9. So that gives us 4 records. Perfect that's working nicely.
• 02:45 Let's try it the other way around. We'll say =COUNTIF
• 02:49 here's our range again from A4 to A11. The criteria, open our quotes, we're going to say less than or equal to five, close our quotes.
• 03:00 Close our brackets and hit Enter and we get five records. So less than or equal to five
• 03:06 we've got 1234 and 5 that makes five records that's perfect works nicely.
• 03:12 And for reference when we're actually working with the quotes around this, where we didn't use those in our first formula?
• 03:19 We'll show you that they will actually work just the same with quotes around it
• 03:24 there as well so it might just be best to get in the habit of always using quotes.
• 03:28 Now the last piece of conditional math that we're going to look at is we're going to look at one more formula and this one is called SUMIFS
• 03:35 and it has an "S" on it. So when we hit SUMIFS here it's going to ask us
• 03:40 what range would we like to sum? So we'll go and we'll sum the purchases right now.
• 03:45 When we hit our comma it asks us for a criteria range again. It's nothing unusual here. So we'll select our criteria and we're going to say
• 03:53 we're going to grab customer numbers that are greater than or equal to two
• 03:58 and then when we hit comma it asks us again, we can put in another criteria range. So lets select the same thing again
• 04:05 and this time when we go comma you can see it's asking us for our criteria in our tool tips. Now I'm
• 04:10 going to actually also ask for all customer numbers that are less than or equal to five.
• 04:15 So what SUMIFS does is it allows us to provide multiple conditions and it will evaluate to make sure that each of those is
• 04:23 true and return any records where that's the case. So it's a little bit different than SUMIF and COUNTIF
• 04:28 because those only work on a single condition. This one actually works on multiple.
• 04:32 When we Enter you can see that 12,603 is definitely a subset of our 18,496.
• 04:39 The only challenge we have here is that if we do want to send our file to somebody who's using Excel 2003
• 04:46 we need to reach to a different formula called SUMPRODUCT
• 04:49 in order to actually get a file that's going to work for them with multiple condition testing.
• 04:52 That's extremely tricky, there's lots of information about it on the Internet but the SUMIFS and COUNTIFS function
• 04:59 did not exist in Excel 2003 and earlier.

Lesson notes are only available for subscribers.