1 00:00:01,150 --> 00:00:06,970 Now let's take a look at how we can count based on a condition. Because you can come across situations 2 00:00:06,970 --> 00:00:12,800 where you don't just want to count the number of values in a range but you want to count them 3 00:00:12,910 --> 00:00:19,160 if they meet one or more conditions That's when you can use the "CountIfs" function. 4 00:00:19,240 --> 00:00:21,310 If you start to type this formula in 5 00:00:21,340 --> 00:00:27,000 so if I just type in "=countif" you're gonna notice there are two different versions of this. 6 00:00:27,060 --> 00:00:34,870 The difference between the two is the "COUNTIF" function can count based on one single condition, the "COUNTIFS" 7 00:00:34,930 --> 00:00:40,210 function can count based on one or more conditions. 8 00:00:40,210 --> 00:00:45,000 So basically "COUNTIFS" can do what "COUNTIF" can do and more. 9 00:00:45,070 --> 00:00:50,260 Now you might be wondering why do we have two different versions if "COUNTIFS" can do what "COUNTIF" can do. 10 00:00:50,260 --> 00:00:56,740 The story behind this is originally Microsoft came up with the "COUNTIF" function and later in Excel 2007 11 00:00:56,740 --> 00:01:03,430 they introduced the "COUNTIFS" function. They couldn't change the original formula because of backward 12 00:01:03,430 --> 00:01:04,620 compatibility. 13 00:01:04,690 --> 00:01:06,790 So that's why we have two different versions. 14 00:01:07,390 --> 00:01:13,120 Now you can just learn the "COUNTIFS" function but since the syntax of these is pretty much identical 15 00:01:13,120 --> 00:01:16,360 and is really super easy to learn you can also learn both. 16 00:01:16,360 --> 00:01:22,270 I'm just going to show you both versions. And what we're going to do here is to solve for these three 17 00:01:22,270 --> 00:01:23,080 tasks. 18 00:01:23,080 --> 00:01:28,510 Our number one task is to count how many missing entry dates there are. 19 00:01:28,510 --> 00:01:30,780 So let's take a closer look at our dataset. 20 00:01:30,790 --> 00:01:37,870 Here we have a column for name, entry date, yearly salary, and previous year's salary. 21 00:01:37,900 --> 00:01:43,930 In the entry date we have a few cells, so two in this case, that have the word missing in there. 22 00:01:43,930 --> 00:01:47,470 These are the cells that we want to count in a dynamic way. 23 00:01:47,470 --> 00:01:51,850 We don't want to count anything that has any other text in there. 24 00:01:51,850 --> 00:01:54,130 So let's start off with the "CountIf" 25 00:01:54,190 --> 00:01:58,820 It only takes two arguments, The first argument is the range. 26 00:01:58,840 --> 00:02:01,440 So the range missing is sitting in. 27 00:02:01,600 --> 00:02:04,540 That's basically this range right here. 28 00:02:04,540 --> 00:02:09,070 The second argument is the criteria that we're looking for. 29 00:02:09,070 --> 00:02:15,460 Optimally, you have the word sitting in a separate cell. In case you don't and you want to put it in the formula. 30 00:02:15,460 --> 00:02:18,540 Don't forget to put in quotation marks, right? 31 00:02:18,540 --> 00:02:25,510 Because whenever we're adding text inside a formula we need to add the quotation marks, close the bracket, 32 00:02:25,690 --> 00:02:26,950 and press enter. 33 00:02:26,950 --> 00:02:29,490 We get two. This is dynamic 34 00:02:29,530 --> 00:02:37,480 If one of these values goes missing, this number updates automatically. Notice that it's not case sensitive. 35 00:02:37,480 --> 00:02:39,730 So I wrote "m" with a small "m" here. 36 00:02:39,730 --> 00:02:43,960 I have it as capital "M" here and it still counts them. As I said before, 37 00:02:43,990 --> 00:02:49,960 ultimately it's good to have the word sitting in a separate cell and then we'll just be referencing 38 00:02:50,080 --> 00:02:54,100 that cell and enter and we get our number. 39 00:02:54,100 --> 00:02:56,200 Now what's the difference with the "CountIfs" 40 00:02:56,440 --> 00:03:02,860 Well, in this case there isn't really much difference except that we're allowed to use more than one 41 00:03:02,860 --> 00:03:03,670 condition. 42 00:03:03,670 --> 00:03:09,990 So our criteria_range1 is basically what we saw as range before. That's this one. 43 00:03:10,030 --> 00:03:14,310 That's where our criteria is sitting on followed by the Excel separator. 44 00:03:14,310 --> 00:03:22,630 Our criteria itself is this.But notice I can put in more arguments, I can add in more criteria to this. 45 00:03:23,100 --> 00:03:28,630 But since I just have one, I'm going to close the bracket, press enter, I get the same number back. 46 00:03:29,020 --> 00:03:32,540 Now let's get a little bit advanced with "CountIfs". 47 00:03:32,560 --> 00:03:37,450 I know this is a basic course but these are topics that you're likely going to come across when you 48 00:03:37,450 --> 00:03:39,570 start to use Excel on a frequent basis. 49 00:03:39,620 --> 00:03:42,610 I thought it's important to add them to the basic course. 50 00:03:42,610 --> 00:03:49,940 Our next task is to count how many people started to work after the specified date here. 51 00:03:49,990 --> 00:03:58,550 So again because I just have one criteria I can use either the "CountIf" or the "CountIfs" version. 52 00:03:58,600 --> 00:04:05,110 So now I'm going to use the "CountIfs". My first argument is the criteria range. 53 00:04:05,110 --> 00:04:11,260 And basically what I'm looking at counting here are the dates and I want to count how many dates 54 00:04:11,350 --> 00:04:14,860 are after the date that I specify here. 55 00:04:14,860 --> 00:04:22,690 So my date range is what goes in my first argument. My second argument is the actual criteria itself. 56 00:04:22,690 --> 00:04:32,650 Now can I do this and close the bracket? What do I get? I get one because there is one person that meets 57 00:04:32,650 --> 00:04:40,090 this criteria whose entry date is actually the same date as this. But that wasn't my task, right? 58 00:04:40,090 --> 00:04:48,100 My task is how many people started to work after this date which means I need the greater than sign 59 00:04:48,550 --> 00:04:49,520 in here. 60 00:04:49,600 --> 00:04:50,940 Can I press enter now? 61 00:04:51,280 --> 00:04:52,570 Let's try it. 62 00:04:52,600 --> 00:04:54,510 Excel doesn't like it. 63 00:04:54,640 --> 00:05:01,580 Now here's something you need to remember. Excel doesn't like it if you add the operator, so the greater than 64 00:05:01,580 --> 00:05:09,080 or the smaller than operator right after the Excel argument. If you need to do that, like we need 65 00:05:09,080 --> 00:05:16,490 to do that in this case, we have to treat it like text and we have to put it in quotation marks. 66 00:05:16,490 --> 00:05:23,030 Can I press enter right now? Excel still doesn't like it. 67 00:05:23,050 --> 00:05:25,750 What's the problem here? 68 00:05:25,750 --> 00:05:31,060 Well I'm planning to combine something that's text with a cell reference. 69 00:05:31,060 --> 00:05:39,400 This means I need the "&" operator here. Now when i press enter, it works. So keep that in mind if 70 00:05:39,400 --> 00:05:43,990 you need to look for anything that's greater than or less than in the "CountIfs" formula 71 00:05:43,990 --> 00:05:50,620 Or in any Excel formula where your operator comes right after the Excel argument. 72 00:05:50,800 --> 00:05:57,400 So for example, in other cases where the operator doesn't come after the argument you don't need to treat 73 00:05:57,400 --> 00:05:58,450 it as text. 74 00:05:58,510 --> 00:06:04,240 Let's say I just want to check if this number is greater than 14. 75 00:06:04,300 --> 00:06:07,710 I don't need to put the operator in quotation marks. 76 00:06:07,870 --> 00:06:15,480 If I press enter, I get false because it's equal to 14. I can say greater than or equal to and 77 00:06:15,490 --> 00:06:16,230 press enter. 78 00:06:16,240 --> 00:06:18,100 I get true. 79 00:06:18,100 --> 00:06:22,710 Here I don't need the quotation marks but here I need the quotation marks. 80 00:06:22,800 --> 00:06:28,210 Now another way around this is to put the operator in the cell. 81 00:06:28,210 --> 00:06:36,220 Then I can take it away from here and press enter and I get the same number. Right now this date here 82 00:06:36,310 --> 00:06:39,010 is not included in my analysis. 83 00:06:39,010 --> 00:06:45,970 If I also wanted to include this date I have to say greater than equal to this number 84 00:06:46,120 --> 00:06:50,200 and then I include the person who also started to work on this date. 85 00:06:50,500 --> 00:06:56,800 So it really depends on your analysis and if you want to include the criteria that you're looking for in 86 00:06:56,800 --> 00:06:59,470 the final answer or not. 87 00:06:59,470 --> 00:07:05,470 Now let's get a little bit more advanced on this and let's count how many people earned a salary between 88 00:07:05,470 --> 00:07:07,540 the values specified below. 89 00:07:07,540 --> 00:07:15,370 So from 80,000 to 100,000. Can I use the "CountIf" function here? 90 00:07:15,370 --> 00:07:20,040 I can't, right? Because "CountIf" can only test one condition. 91 00:07:20,290 --> 00:07:24,560 I have to use "CountIfs" because I have two conditions in this case. 92 00:07:24,670 --> 00:07:27,270 What's my criteria_range1? 93 00:07:27,280 --> 00:07:28,540 What am I looking for? 94 00:07:28,540 --> 00:07:35,300 I'm looking for salaries and these are yearly salaries so I need to look in this range. 95 00:07:35,310 --> 00:07:40,770 My criteria one is this number but it's not exactly this number. 96 00:07:40,780 --> 00:07:44,750 It's any number that's greater than this number. 97 00:07:44,800 --> 00:07:48,900 So again I need the quotation marks followed by the greater sign 98 00:07:49,060 --> 00:07:55,930 and if I want to include 80,000 in my analysis I'm going to add the equals sign as well, quotation, 99 00:07:56,110 --> 00:08:01,980 use the "&" operator to connect the text to the cell reference. 100 00:08:02,020 --> 00:08:07,220 Right now, If I leave my formula I just closed the bracket, I leave the formula to make sure it works. 101 00:08:07,480 --> 00:08:15,490 I see the number 13 so there must be 13 people or 13 numbers here that are greater than or equal to 102 00:08:15,570 --> 00:08:16,820 80,000. 103 00:08:16,840 --> 00:08:24,520 Now I still have one more criteria and that's if the number is less than and let's say also equal to 100,000. 104 00:08:24,690 --> 00:08:28,470 So I can just go build on this, add the next condition. 105 00:08:28,570 --> 00:08:35,030 My criteria_range2 to is the same range here and my criteria2. 106 00:08:35,260 --> 00:08:36,809 Is this one. 107 00:08:36,820 --> 00:08:44,290 But it's not exactly this number. Only if it's less than or equal to this number. 108 00:08:44,290 --> 00:08:46,520 So I need the "&" sign here as well 109 00:08:46,520 --> 00:08:52,090 press enter. And I get five people who earn an income between these two. 110 00:08:52,090 --> 00:08:59,070 If I change this to 200,000 I get twelve people. 111 00:08:59,170 --> 00:09:03,310 Okay so these two formulas are a little bit more advanced. 112 00:09:03,310 --> 00:09:06,460 It's something that most people cover in an advanced course. 113 00:09:06,670 --> 00:09:11,050 And if you find them too difficult for you to digest right now, leave it for later. 114 00:09:11,110 --> 00:09:17,530 But just know that it's possible to easily adjust the "CountIs" formula to count for more than 115 00:09:17,530 --> 00:09:24,180 one condition and also conditions that require operators like the greater than or the smaller than signs. 116 00:09:24,850 --> 00:09:25,820 In the next lecture 117 00:09:25,840 --> 00:09:29,050 we're going to take a look at the sum and the average functions.