Using COUNTA to evaluate and monitor your data - Microsoft Excel Tutorial

- [Instructor] counta is a function that never gets any credit, but if you learn how to use it and use it creatively, it's invaluable. Let's look at this. We've got all of this data, we got this grid, and our assignment is to get it filled in. Delilah Aames. The email address is missing, the ID is missing. Scroll down. For Nila Bush, we have an email and no ID. Let's scroll back up. I'm not even going to scroll all the way down to the bottom of this data to see how many records we have. We're going to use counta to figure that out. Counta is also going to help us assess the size of the job that's ahead of us with getting this cleaned up. Do we have so much that it's not worth trying? Or maybe this is something we can handle in two hours. Or maybe we need three days, we don't know. But the last thing you need to do is just dive in to something like this without knowing how big the job is that's ahead of you, so let's get started. One thing we do know, every first name and last name cell is filled in. We don't know how many start dates, email addresses, and IDs we have. Now let's go to figure out our total records. Equals, counta, open parenthesis. Also notice that the data is in tables, and counta, we want to count the number of first names. Enter. There are 362 total rows of data. Wow. How many start dates do we have? Let's go down here and do the same thing again. Equals, counta, now what counta is doing is, it is counting the number of non-empty cells. So if a cell has something in it, it'll be counted. If not, it won't be counted. Alright, counta on the start date. Go over to the column, wait for that black arrow, highlight the column, Enter. We have 255 start dates out of 362. Let's get the email addresses. Equals, counta, double-click, Email, Enter. Equals, counta, double-click, ID, Enter. Next, how many empty cells are we faced with having to fill in? Equals, the 362, I'm going to put this in absolute cell reference form by hitting F4 minus the number of start dates that we have. And it's hard to get behind there, so I'm going to have to click here and then use an arrow to slide over. Enter. Ooh, we got to find 107 start dates. And now drag this down. We are missing 203 IDs. Equals, sum, open parenthesis, here we go, Enter. There are 539 cells that need to be filled in. Well how many complete records do we have? "Complete" means we have a start date, an email address, and an ID. Counta, alright. I'm going into the Complete column in this table. Equals, counta, open parenthesis, I'm going to grab all three of these columns in this row, and Enter. Okay, so we have one cell, and that's the start date. Here we have an ID and a start date, so that's why there's a 2. Alright, so now here is what I'm going to do. Wrap this in an if. Equals, if, open parenthesis, that counta value, is less than three, then stay empty. So double-quote, double-quote, comma, otherwise, double-quote, capital C, double-quote, close parenthesis, Enter. Alright, now I'm going to highlight that column and center. Now equals, countifs, open parenthesis, criteria range. I want to count the number of Cs in the Complete column. Now comma, double-quote, capital C, double-quote, close parenthesis, Enter. Out of 362 records, we have 84 that are complete. There's a lot of work ahead. Now let's look at one other thing that we can do with counta. We want to measure the attendance level for these people and these meetings. If everybody went to every meeting, how many meetings would that be? That would be equals, counta, open parenthesis, count the number in this column, close parenthesis, times, counta, open parenthesis, and drag here. See, I am not going to count these myself. I'm going to let counta tell me. And then close parenthesis and Enter. Let's check this out. I'm going to highlight, we have eight people, and then 10 meetings, so yes, eight times 10, 80. Now equals, counta, open parenthesis, highlight this grid, and Enter. Out of a total of 80, there were 53 attendances, okay? Equals, 53 divided by 80, equals, alright. And let's make this as a percent and increase the decimal places. 66 and a quarter percent attendance. And then we find out that August did attend that meeting. Notice the numbers are changing, they are updating. And let's add another date. So we have the 25th of May '21. Okay, now there's 88 possible meetings, and Angela attended, August attended, K.J., Pippen, Ravindra, and Wen attended. These are just some of the ways that counta can help you in managing and analyzing your data. They can help you keep track of your progress, it can help you know big a job is before you get into it, it can count lots of cells and save you the hassle of counting them yourself. So get to know counta.