The COUNTIF Function – A Mega Useful Excel Function | Alan Murray | Skillshare

The COUNTIF Function – A Mega Useful Excel Function

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
7 Lessons (32m)
    • 1. Introduction

      3:00
    • 2. Using Cell Values with COUNTIF

      4:58
    • 3. Count Values Between Two Dates

      2:58
    • 4. Prevent Duplicates in a Range

      4:12
    • 5. Compare Two Lists to Identify Missing Items

      4:24
    • 6. VLOOKUP for the Last Match in a List

      7:40
    • 7. Uniquely Rank Items

      5:05

About This Class

7ad9a3be

This class will demonstrate six examples of the mega useful COUNTIF function. This function has helped me so much over the years in accomplishing many an Excel task.

I hope this class will help you understand the versatility of the function and how it could help you even beyond its typical use.

The lessons will include using COUNTIF to count values between two dates, prevent duplicate entries, uniquely rank items and also compare lists.

This function can be combined with others to extend its power. An example in this class shows it used with VLOOKUP to return the last match in a list.

So dive into the class. I will see you there.

Get in touch if you need any assistance.

Transcripts

1. Introduction: hello and welcome to this class on the count if function now. This is a mega useful function that has helped me out many times over the years in achieving Excel tasks. So in this class we're going to see six different examples off its use. We're going to begin with using account if, for it's typical use on, we're going to use it with sale values. So you conniver right, the criteria inside off the function. But we will look at referring to sell values so that it's that little bit more dynamic for Excel. Users would then be using its account values between two dates. So we have to criterion at this point using some typical date analysis, which a lot of people need in their jobs. We're going to look at preventing duplicate entries. Another classic at thing that people come across in their work is Jupiter entries, and Excel has got various ways of how we can remove them or identify them, but in this example will be looking at preventing them as prevention. If possible is better than the cure. Would then be looking at comparing lists yet again, a very common business requirement in my day to day job at training and consulting. The amount of time is read to compare or combine lists for different reasons. Here it will be comparing them with count. If toe identify items that are missing, we were them. Use it with V. Look up now. V look up excels. Most well known look up function would always return from the first value it finds in a list. But if we combine it with counts, if we can return any value, we want the second, the third, the fourth and in the example in this class we will use for the last match in a list. But when you learn that technique will be able to return, anyone we want first, last fifth does not matter and then finally will use it to uniquely rank items. So some kind of league table or ranking table off people were teams or grips of some kind. Excels got some rank functions, But what if people have the exact same kind of score or points or sows amount? Whatever the thing is that your ranking them by, we'll count if can help us uniquely rank them in the purposes of creating those kind of league tables. So if that sounds good, let's get into it. Dive in. I will see you inside the class. And if you need anything, please let me know. 2. Using Cell Values with COUNTIF: hello and welcome to this lesson where I would like to start looking at using account if function with sale values. So a lot of people, when they're learning this group of functions, count if averages and some if I can get a bit confused with how the criteria is entered because it's a little bit different to your standard. If function, we can only into the criteria as a string or refer to a sale reference. But what if you want a combination of the two? So I've got a couple of examples here to demonstrate this I have some amounts on. In the first example, I would like to calculate how many off those amounts are 10,000 or more. Or, to be more precise, how many are bigger or the same as the value in cell F free. Want to use that value? So in cell G free. Let me zoom in for this. Let's start account if function so that it will promptly for the range to test. And I would like to test column D here, so that's going to be D two down to be 13 and I put in my coma, said unprompted for the criteria. Now I want to use the value in that cell, but I want to know is greater than or equal sets your value. Now I could write the greater or equal symbols in that cell, but that could be a bit of an awkward were working at times. So what I'm going to do is in a string of instant double quotes. I'll type the great and R equals element. They're not putting the and percent to join or come cata night the value from so Jeffrey. So by joining stuff that a type which must be into this a string and the reference to sell Valium allowed to do both those things. If we want a combination, I can just join them together and create and form my own criteria. So if I was to press enter on that function, I can see that there seven on By choosing £10,000 it's relatively easy to spot that there are indeed seven. But if I change value f free, so maybe 13,000. They're my formidable run and say that there are four, so there are only four that of 13,000 above. So I made my formula dependent on a cell value that have managed to keep it the sale value just numeric, you know, just with the number like 13,000 and catch my logical simple separate to that they're built in as a string in the function but joining them together to create that criteria. Now that's look at another example of that climate technique where in cell g six, instead of referring to sell Now, really, I want you future diets Now, At the time of doing this video, it is the twin Schiff off January 2018. On these dates are a mixture of previous and future dates. So in cel G six in we go to make it nice and clear on screen on its account if function and this time they're range are the range off dates. So that will be C two to C 13. I could have taught that and that will put the comma in. So they rolled into the criteria element. Now, this time in a string from going to type greater then. So this time just bean specific. It must be a future date. No interested. This ends today must be a future and it goes on and purse end. And this time it's the today function. So I'm not actually using a sale value this time. I know let's take on the title of the video title off the lesson, but I must using a function so knowing that we can bring the reason to play as well. I can do a mixture off using a function, but also joined on to that. My logical symbols, my less than or greater than or equals or not symbols. We can create a criteria on the fly, a mixture off Constance static data, but also more dynamic things like cell values and functions. And we can take care counted functions to another level. Here we go. I have six future dates. Remember, it's a twin chief of January 2018 this video. So there are the six future dates 3. Count Values Between Two Dates: now just following on from that previous lesson, we saw an example where the counted function can calculate how many dates were in the future. So here I've got a similar set up with some dates and cells, and I want to use a date range. So now we have two days. Is it greater or equals to this one, but less than or equal to this one? Now these This creates two separate conditions. So in this example, let's go straight into it. In cell F three, I need to count ifs function. Now I know this section off the course is about count. If, but I'm sure you'll allow me to bring Count ifs into the equation as well for this quick example, because now we need to get to criteria, ranges and two criterias. So criteria range one is the range of cells got them and then criteria. When I put my car, Marin is going to be so d free. So put my stringing greater than or equals. I am sand d free. We move along so we can see what's going on into a comma, and this box below is prompted me for criteria range too now to save. Scrolling around is going to top that in a two to a 12. It's the same Rangers before I can see it there, comma. This time I'm going to say less than Well, I got my quotes there. Not gonna worry about equals this time, just a demonstrate kind of mixing up a bit. I'm ampersand and then a reference that in date, then I can put my close bracket for count ifs, and if I pressed enter. Apparently there are three. And if I just scrolled out of this for a moment four for December 15th there are free that could a sow's free dates within that range. But if I was to change cell value to maybe the second here, that should bring in another one because there is one here in the third of December, then that Recalculates to for So by using the counts, its function on owners, multiple conditions we can follow on from the previous examples and create ranges such as this date range our input that by users creating a dynamic formula 4. Prevent Duplicates in a Range: hello. In this lesson, we will look at how the county function can be used to prevent duplicates in a range. Now, handling duplicates is always a common issue among Excel users. On overtime, Excel now has the functionality to remove duplicate entries or to highlight them with conditional formatting laws. But surely, if in a position to do so, it would be better to prevent them in the first place so we could use a count your function in a data validation rule to do that. If I was to select sells a two to a 10 she would highlight the rains that you want to put the rules within. And as you can see here, I have some names and I would like to prevent a duplicate name. So we're popping to the date tab, and I would go to my data validation button, and I would choose custom from the validation criteria list said It props me for a formula . Now you're probably not going to be able to see the formula. I write very well in that little box provided. So I'm just going to come out of here for the moment and right into a cell and I'll go back in and placed it there. Let me zoom in a little bit as well. This is what we're going to do. We're going to use that count if function equals count. If and father range, I'm going to reference a to the first sell off my range. It's important. Use whatever the first cell of your highlighted range will bay, and I want to highlight a 2 to 10. So I was the 1st 1 and of maybe absolute and then going to put in a colon and just put a to So I'm leaving that side. Relative have got a fixed starting position for the range on a relative ended in position so that when we're highlights sells a 2 to 8 10 The first cell being a to that ranges expandable. So for the first cell was testing may to second on its aged Xhaferi. Third on it's a two to a four. It's the second part of ranges Flexible. I can then put a comma where account, if function written either will prompt me for the criteria and a criteria. Old type YouTube. So the active sale, the first sell off the range of you closing bracket and then equal to one. So that will be a validation criteria. So what? I'm doing really awesome counting How many times, Uh, the value from the current cell is in the range from A to two. The current cell, if it is equal to one and then that's OK. Uh, if it is not, then therefore it must be greater than one. Therefore, it must be a duplicate and to prevent it. So you want to take a copy off that formula and then I'll highlight my range again and I'll go into my data validation rules. Shoes custom From what? To allow and paste the formula into that formula box offing. Click OK, and the validation criteria is applied. So now if I was to type a a new name capital, then that's okay. If I was to type something like Sally, I get my warn message now prevented because that is invalid criteria and that is how we can prevent duplicate entries in the range using the county function 5. Compare Two Lists to Identify Missing Items: Hello. And in this lesson, we will use the count if function to compare two lists. Andi, I have a list on one sheet hit list of names and on lists to I have another list of names are slightly larger list. And my goal here is toe. Identify which names, if any, a care on list one. But do not occur on this too. So I'm looking for a kind of miss mismatched entry, if you will. I'm going to achieve this. We've count if let me start with the formula in Selby to what I should say. Column B LME may move it to another level. Let me begin with the counts if function and it will prompt May for the range to test. And that is the range of cells on this. To see every Hilal the names on this too. And make that range absolute. And then I'll put in my comma. Said it prompts me for the criteria and the criteria is simply they first sale on this list Now don't need that sheep reference there, so I'm going to remove that. Here we go. So you to tow a 10 on this too. How many times does a to a clarinet Now, If a to is to occurred, then that means that it is there. If the answer comes back zero, then obviously that name is not there said by presente. And copy this down. I can see that it's the names in rose 34 and five, which are not on your list. Where is the names? Inroads to six and seven are on that list, so I have identified them with that counted function. Now, if we want to take this a different approach now, this is using a formula so I can get specific values to appear in a sale or something like that as my result here we just got once and zeros, but that can be adapted. But let's imagine if I go into that formula and just take a copy off it, that my goal is to change the color off the cells. I won't use a conditional formatting roll toward identify using color. What names do not occur in the other list, so it highlight the range of cells that I'm applying the conditional formatting rule to I would go into my conditional formatting new role use a full Miller to determine the cells to format and paste in the formula, which I have now. It tells me here that will only format the values if formula is true. Now, mind at the moment presents results as one and zero. Really, they are. There are there aren't. So I'm really interested in here is if the result is equal 20 and now I just turned into a condition by simply typing equals zero after the formula that I have. If I now choose some kind of formatting rule, or just go for maybe a kind of blue color and apply this and I can see the blue calories applied only to those names that have not occurred on the other sheet. So all I did order formula there was just put on equal zero in the end to turn it into a condition rather than a full minute return of value. This column is no longer needed. I can get rid of this column that's no longer needed anymore, as my conditional former enrollees in place and if anything else was toe happen. You know, if I was to go to list to and remove one of those names like Katy Perry from there and come back then. Her name has been highlighted in blue as she no longer occurs on both lists. 6. VLOOKUP for the Last Match in a List: hello. And in this count, if function example, we would like to use it with V look up, but to return the last matching value from a list. So when you use look up for Mueller's like V look up. They always locate the first addition of value. And normally that's OK, because you're looking for something unique. Let's imagine a scenario like this one where I want to return the total. So the total sales value here for a specific customer, i d. And let's imagine that I want 1094 which is a company called Han. Our economies on day occur three times in this list, So you really want to return that 800? That is the final time that the customers used. I don't the 1st 1 on the last one, whatever that is. And this is where count if can come in and help us out. Please bear in mind that it doesn't have to be the last match. Using this technique, you could find a second match with 1/5 match or something a bit more specific, if that made sense in a scenario right now, though, the last one So the first thing I'm going to do is to use this number off matches cell hi h one And I'm just going to use accounting function to simply count how many times that 109 for occurs. It's gonna put a simple count if in here to look at the range of customer days and return. How many times Wonder in 94 or Serco G four close. And that's just going to come back and say that it cost three times that, like have already mentioned. We know that. But because it's now I formula, that's going to look after itself over the long term. Now I need a V look up to 10 the value off that you know, the third occurrence off custom 1094 now for this to work, I want to insert I help a column, which is always a useful technique, and what I want to do here is I'm going to insert this column in between see and date. This could pretty much go anywhere within reason, but what it what it is a combination of the I D and the like occurrence eso just put instance, try to think was shorter name to fit inside that cell and that it matters that much on all against do is simply put equal sign. Refer to the i D. Then an M percent to come. Katyn. Eight onto that. The occurrence. So I'm Dante is accounted function very similar off a, basically the same as what I did with that preventing duplicates lesson against Get it to look at the range off. See, too, in an absolute manner Colon and N. C. Two. In a relative manners, it's an expand in list as a copyist. Formative. Next, sell it so to to see free that the next one is C two to C for next, when I c two to C five and so one on the criteria is also see to So they can you count how many times that I D has been mentioned within this range. Always expense and come cap tonight that on to the idea. So now we'll get 102 one since the first time 102 is being used. But as I copy this down, you get to hear, I'll get 10942 So got 1094110942 It's the second time that's been used. Now this column could be hidden when it's all said and done. You don't need to see this. You don't want to see this. Typically, this would probably start a table and hidden robin in the middle. You know this. There's no set rule here. As long as we bring this in and I'll just copy at the bottom. I'm getting all these errors come up with a supposed errors. Oh, no, no. Said was well, due to our potentially emitting adjacent cells, my formula, that's because I've got at flexible ranged absolute relative ended a range. I could just ignore those. Not that they're doing any harm, either. Really? But now that's in play. I can come back here and do my veal a cup. So this is all gun happened in cell ire for here. Start up my V. Look up. What did you look up value? One look at value is a combination of the I. D and the occurrence. So what I'm actually looking for is 1094 And if you conjoined onto that, the last occurrence, which is three on which is what I calculated in So I want and put my cover ways to table array wealth because I'm using V. Look up the table or a must start from column day because that's where of port My look up column Really my help of column I look up column that What If you wanna refer to that ah, hall at the range and I'll make that an absolute difference. So look inside this range for it. Total number is going to be a three. So looking down day, then you got these two of his three and it's going to be a precise match. So this people focus on the in there. And if I press enter, I get the £800 that we were expected. Come back outta here. There it is that £800 for Connery Conners there. That was the third edition of it at his 1094 three. So that is how we can use Countess to assist a look up formula like V look up or any of the others you may know to bury achieve their second occurrence or something, or the last occurrence of something Not always the 1st 1 Now, as you can probably imagine, you don't even need these cells on your screen. I ever, Really. I mean, if I waas to just doing that into my V look up formula. So instead of reference in I one if I just put that formula in there and I don't necessarily even need to that's my absolute don't necessarily even need that kind of help. Assailant stuff Depends how you want to work. Still 800. I don't even need that stuff. I could clear the out if you want to work. Sometimes we're using to help us. Sell was, it does make it easier to come back and read the formulas in the future and to work with the staff and analyzes stuff rather than nest in the mean the same for help of columns as well. To degree, this array examples off this stuff, but it's much easier if we bring this in. We can hide it when we no longer want it. So this is a kind of finished article that's its behind the scenes doing its magic. Now I'm able to return where the last occurrences off that I d at any point in time 7. Uniquely Rank Items: hello and welcome to this lesson on using the counts if function, to create unique rankings. So I have this list of names and screen and how many points they have attained for doing whatever they are doing, and I would like to create a unique ranking for them. So I have this column C to create an initial rank and then comedy for a unique rank, and they're probably hired column C because I will not need it anymore. I don't need both. Let's quickly get that formula running. So Excel provide a few different rank functions, not just what the standard compatible one. If you will hear that, will probably for the number two rank, which is that points total for Barbara, a reference to the range of cells, which we B two to B 11. Right now, I'll make the absolute and if only a reference to the order off the rankings. I would like to use descended, so the larger the value, the better the ranking. So I'm going to choose zero for descending, and I'll run that formula, which, if I copy it down, we'll have Freddie Mercury and Position one. Has he got the largest points total of 40 down to fuel Collins, who finished 10th out off 10 because he has the lowest points total 25. But you may notice that I have to on six of Michael and no scored six or so ranked six because they scored 30 points each. You may also notice that Number 70 is missing because the rank formula skip that because off that time and maybe that is true to form. But if I'm using the look up formula to grab these rank values to order them in some kind of league table, then the fact that it's not a unique value, it makes it not good enough. So in column day, you were going to separate them of a unique rank to do this, the formidable reference to rank value. And I'm going to add onto that the Countess function. So this is what we're here for. We want to find out in this section the different, flexible and powerful Ways account if is used, and once again I'm going to type of formula very similar to the previous videos, is going to have the absolute reference. So the first cell in that range see, too, and then a relative reference to the first selling that range. See, too, to quite this ever expanding range. I'm going to ask it to count how many times that rank value occurs, and I'm going to minus one from the answer. And if we run this formula and then we'll see how it works. So festival at proof that it does work and that no has been assigned seven because there already is some one on six, therefore creating a unique rank. But if we look at knows value a little bit more closely to figure out how this works, you can see how XO colors the range being used to identify a bit easier. So what it's doing this is looking at knows, ranked value, which is six. And it's adding on to that. How many times it's occurred in that range so far between the fixed point off, See, too, and the current selloff C seven. So by now it's occurred twice is the second of cones, so adds on to it too minus one. So I always back end up adding one on top. So it's doing six plus one, which is obviously seven But if this is the third time it occurred, it would have looked to that. You know, you know, this is free months. One is to I would have come back of eight and so on, and this is how it works. So even if Mariah Carey was suddenly to score 30 points as well, she's ranked number eight because this is now the third time that I was occurred. So it does. Six, you know, plus every times for you minus one is to six. Plus two is eight and this is how it works. Crooks, that unique ranking at this point, that initial rank formula can be hidden. It doesn't have to be in that position. It could have been even on a different sheet. So it's tucked away and nobody sees this stuff. You would just have this one ranking, but it's actually working off that kind of help a column and then count if coming in to establish that unique ranking