1 00:00:01,330 --> 00:00:07,030 Let's take a look at how we can sum or average values by criteria. 2 00:00:07,030 --> 00:00:13,810 So right here we have the actual sales by different apps with their region and their division listed 3 00:00:13,810 --> 00:00:14,350 here. 4 00:00:14,380 --> 00:00:18,660 Our data set goes all the way down to line 167. 5 00:00:19,060 --> 00:00:21,910 Now based on this I want to solve for the following. 6 00:00:22,180 --> 00:00:28,590 My first task is to get the total sales for the app specified in this cell right here. 7 00:00:28,670 --> 00:00:31,110 Similar to what we saw with CountIf 8 00:00:31,150 --> 00:00:40,270 by conditions, we also have two different choices here. We can use the "SumIf" function or the "SumIfs" function. 9 00:00:40,330 --> 00:00:46,900 The "SumIfs" function came since Excel 2007 and it can account for more than one 10 00:00:46,900 --> 00:00:48,020 criteria. 11 00:00:48,020 --> 00:00:53,590 But if you have one criteria you can use either of these formulas but there is one thing you have to 12 00:00:53,590 --> 00:00:58,810 be aware of. The arguments required for these two formulas are different. 13 00:00:58,810 --> 00:01:03,340 Let me show you by solving this first with this "SumIf" function. 14 00:01:03,570 --> 00:01:10,540 So just type in SumIf, open the bracket, the first argument is the range but it's not our sum range 15 00:01:10,540 --> 00:01:12,770 because the sum range is right here. 16 00:01:12,820 --> 00:01:18,640 It looks like this is the range where the criteria is sitting on. My criteria is "Perino". 17 00:01:18,640 --> 00:01:20,440 "Perino" is an app. 18 00:01:20,440 --> 00:01:21,990 So it's sitting somewhere here. 19 00:01:22,000 --> 00:01:25,360 This is the first argument from my SumIf function. 20 00:01:25,360 --> 00:01:32,860 The range of my criteria. Highlight this, control+shift+down, control+backspace to jump back up. Next argument 21 00:01:32,890 --> 00:01:34,640 is the criteria itself. 22 00:01:34,660 --> 00:01:36,560 In this case it's sitting in this cell. 23 00:01:36,670 --> 00:01:44,140 And the last argument is my sum range which is now my actual sales. So control+shift+down, control+backspace 24 00:01:44,140 --> 00:01:47,320 to jump back up, close the bracket, press enter. 25 00:01:47,320 --> 00:01:50,010 That's the total sales for Perino. 26 00:01:50,530 --> 00:01:54,750 Now let's also solve this with the SumIfs version. 27 00:01:54,850 --> 00:01:56,060 Here's the difference. 28 00:01:56,290 --> 00:02:03,070 When we opened the bracket we see the sum range first and then we see the different criteria ranges. 29 00:02:03,100 --> 00:02:08,830 Microsoft had to switch over the arguments here because they don't know how many criteria ranges you're 30 00:02:08,830 --> 00:02:11,650 going to have but you're always going to have sum range. 31 00:02:11,650 --> 00:02:20,740 So they had to bring the sum range forward in the argument list which for us is our actual sales. 32 00:02:20,740 --> 00:02:24,420 Next argument is the range where our criteria is sitting on. 33 00:02:24,430 --> 00:02:27,910 So this is the app because our criteria is an app. 34 00:02:27,910 --> 00:02:30,400 Its range is right here. 35 00:02:30,400 --> 00:02:34,920 Last Argument is the criteria itself which is Perino here. 36 00:02:35,020 --> 00:02:35,820 Close bracket. 37 00:02:35,830 --> 00:02:37,450 press enter, okay. 38 00:02:37,600 --> 00:02:44,010 So don't forget that the order of arguments is different between the SumIf and the SumIfs versions. 39 00:02:44,080 --> 00:02:46,740 Now the great thing about this is it's dynamic. 40 00:02:46,780 --> 00:02:55,650 If I change this to Arcade, I get the sum of Arcade. Change these to Pes, I have the sum for Pes. 41 00:02:55,900 --> 00:03:02,080 Next task, let's get the total sales for the division and region specified below. 42 00:03:02,080 --> 00:03:08,470 So right here we want to select or type in the division. We want to type in the region and we want to 43 00:03:08,470 --> 00:03:10,380 get the total sales. 44 00:03:10,450 --> 00:03:18,130 So we have two conditions which means we need to use which version? The SumIfs version because that allows 45 00:03:18,130 --> 00:03:20,540 us to have multiple conditions. 46 00:03:20,560 --> 00:03:28,660 Sum range, actual sales, criteria range 1. That's either going to be our division column or the region 47 00:03:28,660 --> 00:03:29,350 column. 48 00:03:29,470 --> 00:03:31,330 The order doesn't matter at all. 49 00:03:31,330 --> 00:03:34,510 So I'm just going to start off with the division column. 50 00:03:34,510 --> 00:03:39,490 Next is the criteria itself which is sitting right here. 51 00:03:39,490 --> 00:03:42,040 I have one more condition and that's the region 52 00:03:42,370 --> 00:03:48,320 but first I need the range where this region is sitting on which is right here. 53 00:03:48,790 --> 00:03:55,000 And then the region itself which is specified here, close bracket, press enter. 54 00:03:55,000 --> 00:03:59,690 That's the total sales for division Game in region Europe. 55 00:03:59,710 --> 00:04:06,370 Now our next task is to get the average sales for the division and region specified below and exclude 56 00:04:06,520 --> 00:04:08,050 zero values. 57 00:04:08,230 --> 00:04:12,150 So we can see here we have a bunch of zero values here 58 00:04:12,290 --> 00:04:16,310 and because we want to get the average they're going to influence an average. 59 00:04:16,329 --> 00:04:22,029 So let's say these are actually supposed to be empty and shouldn't be included in our final average 60 00:04:22,029 --> 00:04:23,020 value. 61 00:04:23,080 --> 00:04:30,730 So this time, instead of using the SumIfs function, which function do I need to use? 62 00:04:30,760 --> 00:04:32,190 The AverageIfs function. 63 00:04:32,190 --> 00:04:37,570 Now with the AverageIfs we also have two versions of it we have the AverageIf function as well as the 64 00:04:37,570 --> 00:04:43,870 IFS versions. And the order of arguments is also different for these. With the IFS version 65 00:04:43,870 --> 00:04:46,330 the average range comes in first. 66 00:04:46,330 --> 00:04:51,680 So that's the range we want to get the average of which is our actual sales. 67 00:04:51,700 --> 00:04:58,300 So similar to the SumIfs version. Our criteria range one is division here, the division column. 68 00:04:58,630 --> 00:05:07,890 The criteria itself is our division. The second criteria range is our region and the criteria itself is sitting 69 00:05:07,890 --> 00:05:08,490 right here. 70 00:05:08,640 --> 00:05:16,170 If I just close the bracket and press enter I'm going to be including the zero values in this average 71 00:05:16,170 --> 00:05:16,980 calculation. 72 00:05:17,220 --> 00:05:21,300 So basically I have the exact same formula I had here 73 00:05:21,300 --> 00:05:28,410 Except that I've replaced the SumIfs with the AverageIfs but now I also want to exclude the 74 00:05:28,410 --> 00:05:37,080 zero values. I can add another argument to this at the end. My criteria range is the column where I have 75 00:05:37,080 --> 00:05:44,310 the numbers in and the actual criteria is it should be greater than zero. And because this is coming 76 00:05:44,310 --> 00:05:47,310 right after the formula argument separator. 77 00:05:47,310 --> 00:05:49,420 I have to put it in quotation marks. 78 00:05:49,590 --> 00:05:52,400 Then I need the "&" and zero. 79 00:05:52,410 --> 00:05:58,410 Now you can put the zero value also inside the quotation mark and Excel is going to know to translate 80 00:05:58,410 --> 00:05:59,940 this to a number. 81 00:05:59,940 --> 00:06:06,900 So now when I a press enter I get a bigger value because my 0 values here are excluded. 82 00:06:07,170 --> 00:06:13,530 So that's how you can use the SumIfs and the AverageIfs functions to get these sum or the average 83 00:06:13,620 --> 00:06:16,920 of a range of numbers based on criteria.