Conditional Math: Calculations Based on Multiple Columns as Criteria

Quick reference

• 00:04 It is possible to add values in Excel based on more than one criteria.
• 00:09 If for example, I want total sales, but I only want it for
• 00:13 tens sold in California, well I can use the sum ifs function.
• 00:18 Let's take a look at how that would work.
• 00:21 First, I'm going to freeze my data headers in place on the View menu,
• 00:25 select Freeze Panes.
• 00:27 Now it's going to be easy to see what's going on at the bottom of my sheet.
• 00:31 Okay, you can see in this section down here, I want sales for
• 00:35 California for the items tents.
• 00:37 And then we'll do the same thing for each of the following items listed.
• 00:42 Let's use the sum ifs function.
• 00:44 The first argument is the sum range, this is what we want to sum.
• 00:49 In this case, we're going to add the total sales including tax values.
• 00:54 We'll select those, and then lock the reference using F4 since we'll be
• 00:57 performing the same calculation for the other items in our list.
• 01:03 After the sum range, we have criteria_range and criteria.
• 01:07 We select the range of the criteria, and
• 01:09 then enter the specific criteria value we're looking for.
• 01:13 You'll see we've got criteria_range1, and criteria1.
• 01:17 If we have multiple criteria, you can add criteria_range2 and
• 01:21 criteria2, then a 3, and a 4, and so on.
• 01:25 There is a limit for how many criteria you can use for a formula, but
• 01:28 it's unlikely you'll ever reach that limit.
• 01:31 In this case, the first criteria range is state code.
• 01:35 I'm going to select the state code column.
• 01:37 Just make sure it's the same height as your sum range.
• 01:40 Then lock the reference with dollar signs, and what value does it have to be?
• 01:45 CA.
• 01:47 Second criteria range is your items column, lock that?
• 01:52 What does that have to be?
• 01:53 Tent for this example, and close parentheses.
• 01:57 So I have my sum range in blue,
• 01:59 first criteria_range in red, what the criteria has to be.
• 02:05 Actually, when it's going to look for it in the red range, that's the purple.
• 02:10 Then the green range is my second criteria_range, and
• 02:13 what is it going to look for in the second criteria_range?
• 02:16 This violet kind of color, tent, and this is my sum ifs formula.
• 02:22 Total Sales for tents in California is over $1400.
• 02:27 When we double-click on the fill handle, it'll copy all the way down.
• 02:31 Now, let's look at the count, the number of transactions for
• 02:35 each item and location.
• 02:37 How many times were tents sold in California?
• 02:40 Instead of using sum ifs, we can use count ifs.
• 02:44 And this is as simple as entering the criteria_range and
• 02:47 criteria as many times as you need for all the criteria that you want.
• 02:52 First criteria range is my state code again.
• 02:55 Select those states, lock the reference and select my state code column.
• 03:02 What is the state code here?
• 03:03 It needs to be CA.
• 03:06 The second criteria_range is the items.
• 03:11 Again locked with dollar signs, and the item has to be tent.
• 03:15 And the count ifs will count how many tent transactions there were in California.
• 03:20 Looks like there were three.
• 03:22 Double-click on the fill handle and we fill the rest of the range.
• 03:26 To make it a little more interesting, let's use date as a criteria and
• 03:31 I want to have my total sales column j for the month of May.
• 03:34 And you can see, I've added a date column here to help us out,
• 03:38 you'll understand why in a moment.
• 03:41 I want sum ifs, but what do I want to sum?
• 03:44 My sales column, so let's lock that reference.
• 03:50 Where's my criteria_range?
• 03:52 Well, the way I'm going to do this is to look for sales after a start date and
• 03:56 before an ending.
• 03:58 That's two criteria related to the date value, so
• 04:02 the date will be the criteria range for both criteria.
• 04:07 So the first criteria, the date has to be bigger than or equal to the start date,
• 04:12 which is why I've added this May 1st value here to help us out.
• 04:17 If you're looking for an exact value like we did with the California items,
• 04:21 you don't need to type an equal sign.
• 04:23 But since we're looking for dates greater than or equal to a date,
• 04:27 we need to put that in quotation marks.
• 04:31 I'll reference our start date, I'm going to use the & to combine that so
• 04:35 it's greater than or equal to that date.
• 04:39 Now criteria two refers to the same date column.
• 04:44 Again, lock that with dollar signs, and
• 04:47 the criteria here needs to be less than or equal to & in the end of the month.
• 04:54 Now I don't have the end of the month anywhere but
• 04:56 I do you have the beginning of the month.
• 04:59 And I have a function to get me the end of the month if you remember that's EO month.
• 05:05 May 1st is my start date, and to get the same month and date,
• 05:09 we enter 0 for the second argument and close the parentheses.
• 05:15 Now we have the date for the end of the month.
• 05:17 So what do we have here?
• 05:19 Sum ifs, sum range is what I'm going to sum based on what criteria?
• 05:26 The first criteria range in red, that has to be greater than or
• 05:30 equal to the 1st of May.
• 05:32 The second criteria in the same red range,
• 05:35 that has to be less than or equal to the end of May.
• 05:39 We hit Enter and I've got over $22,000 worth of sales for May.
• 05:46 Let's see if that works for June.
• 05:48 Copy and Paste, and yes, it works for June as well.

Lesson notes are only available for subscribers.