1 00:00:00,970 --> 00:00:04,000 Let's take a look at Excel's Count, CountA, 2 00:00:04,000 --> 00:00:05,650 and CountBlank functions 3 00:00:05,650 --> 00:00:10,700 and when each of these can come in handy. This is the start file for this section. 4 00:00:10,720 --> 00:00:13,300 Let's jump to the first tab called count. 5 00:00:13,300 --> 00:00:20,580 Here we have a small dataset with names of employees, their entry dates and a date here is missing, their 6 00:00:20,590 --> 00:00:26,020 yearly salary, and their previous year's salary. We are going to see what the difference between these three 7 00:00:26,020 --> 00:00:27,980 functions are down here. 8 00:00:28,000 --> 00:00:30,070 So let's start off with count. 9 00:00:30,130 --> 00:00:31,710 Open the bracket. 10 00:00:31,930 --> 00:00:38,660 Now I can either count different ranges separately or I can just give it one range. 11 00:00:38,680 --> 00:00:43,570 In this case I'm just going to reference all the cells above, close the bracket. 12 00:00:43,570 --> 00:00:48,150 Now when I press enter what do you think I'm going to get? 13 00:00:48,200 --> 00:00:49,820 Did you get zero? 14 00:00:49,880 --> 00:00:52,170 Why does Excel do this? 15 00:00:52,190 --> 00:00:56,590 Well, the Count function does not count text. 16 00:00:56,630 --> 00:01:00,550 It only counts numbers. When I pull it here. 17 00:01:00,560 --> 00:01:02,840 What do you think I'm going to get as the answer? 18 00:01:05,810 --> 00:01:06,610 7. 19 00:01:07,320 --> 00:01:10,210 It's counting all of these dates. 20 00:01:10,440 --> 00:01:14,720 Dates are in fact numbers but this one here, 21 00:01:14,760 --> 00:01:15,830 That's not a number. 22 00:01:15,840 --> 00:01:18,370 It's text so it doesn't count it. 23 00:01:18,480 --> 00:01:23,830 So now when I pull it here, what do you think I'm going to get? 8. 24 00:01:23,850 --> 00:01:30,360 These are all numbers and let's put one more time. Six. 25 00:01:30,360 --> 00:01:31,650 I have 6 numbers. 26 00:01:31,680 --> 00:01:35,580 There are two empty cells here and it doesn't count these. 27 00:01:35,820 --> 00:01:38,870 Now what if you actually wanted to count these. 28 00:01:38,940 --> 00:01:48,960 You have to use another function that's called the "CountA" function. "CountA" count numbers and text. 29 00:01:48,960 --> 00:01:51,830 So now I'm going to reference these, close bracket. 30 00:01:51,840 --> 00:01:55,360 What do you think I'm going to get? 8. 31 00:01:55,440 --> 00:01:56,940 So let's just pull this across. 32 00:01:56,940 --> 00:01:58,940 What do you think I'm going to get here? 33 00:01:59,940 --> 00:02:02,260 Also 8, because it counts 34 00:02:02,260 --> 00:02:07,970 text and numbers. When I pull it here it's going to be 8. 35 00:02:08,009 --> 00:02:08,300 Right? 36 00:02:08,310 --> 00:02:17,280 Because these are all numbers. Now here I'm going to get 6 because "CountA" doesn't count blank cells. 37 00:02:17,290 --> 00:02:19,830 Now let's get to our last one. 38 00:02:19,830 --> 00:02:22,050 We have "CountBlank" . 39 00:02:22,050 --> 00:02:29,250 We're going to give it the same range, close bracket, press control+enter just to stay on this cell. 40 00:02:29,250 --> 00:02:34,950 No blank cells here right? Our count is zero. Here we're also gonna get zero. 41 00:02:34,950 --> 00:02:39,120 And here what are we gonna get in that last one? 42 00:02:39,120 --> 00:02:42,800 We're gonna get a 2 because we have 2 blank cells here. 43 00:02:42,850 --> 00:02:45,200 Now if I delete something here. 44 00:02:45,210 --> 00:02:54,750 So if I delete the text in cell A9 what's going to change below? My "CountA" changed and my "CountBlank" 45 00:02:54,750 --> 00:02:55,940 changed. 46 00:02:56,010 --> 00:02:59,160 Now let's say if someone sent you a file. 47 00:02:59,160 --> 00:03:05,220 It was a large file and you want to make sure you don't have any blanks in there. 48 00:03:05,220 --> 00:03:10,800 You can run a check by using the "CountBlank" function if you want to make sure that you don't have any 49 00:03:10,800 --> 00:03:12,270 text in there. 50 00:03:12,270 --> 00:03:20,580 You can run a test by using the "CountA" function and deducting the count function from it. 51 00:03:20,610 --> 00:03:28,740 So if I just write this in one go I would say "CountA" and count these because that counts everything, right? 52 00:03:28,800 --> 00:03:35,610 Text and numbers minus count of these, close bracket. 53 00:03:35,630 --> 00:03:43,440 Press enter, I get one. So you could write your own validations to make sure that your dataset passes 54 00:03:43,440 --> 00:03:44,700 certain checks. 55 00:03:44,700 --> 00:03:50,970 Now of course you can also add data validation to the cells to ensure that people don't type in text 56 00:03:51,030 --> 00:03:56,850 where they're supposed to type in a date but it's always possible that someone can go and just delete 57 00:03:56,910 --> 00:04:04,530 your validation so it's a good idea to run some checks like these to make sure the data pass some basic checks. 58 00:04:04,530 --> 00:04:06,660 In the next lecture 59 00:04:06,660 --> 00:04:11,520 we're going to take a look at how we can count based on criteria.