Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
This worksheet that we are seeing has about 700 records or so, and you are looking at it one day and maybe someone else's looking at it with you and question just comes up, how many different departments do we have in this organization? Hmm. Hadn't thought about that. We might have a list somewhere of them. How many different buildings do we have? Or different way to say this could be, how many unique entries are in column C or in column B? The solution to this is not exactly an easy one and it's not exactly a shortcut, but it is a technique. In one of these years, I would expect that there would be a new function in Excel called Unique.
I am going to put this particular formula above the data here. I wouldn't typically do this, but for purposes of demonstration, I think it's a good place. I am right-clicking on row 1 here and inserting a new row above this. And we are about to write what's called an Array formula. There is one of these situations where I will do this first, and then we can take a look at it. Try and explain it a bit. It will involve a function called countif, which you might or may not be familiar with.
I need to count all the cells in this column from here down to the bottom. Now I am pressing Shift+Ctrl+Down Arrow. That goes down to row 743, comma, and rather than doing that all over again, because I do need to use it again, I am going to copy this with Ctrl+C, put it right here, Ctrl+V, put in a right parenthesis and another right parenthesis and another one and because this is what is called an array formula, a special kind of formula, in effect it allows us to process information across many, many cells at once.
I am going to press Ctrl+Shift+ Enter, and there is the answer. There are 23 unique entries there, starting in cell C3. Now you notice also that the formula as you see it in the Formula bar has braces on it. You don't type this. It almost sounds as if I am making up this description as I go, but I am not. And so an array formula, and they are kind of hard to describe except by example, allow us to ultimately process and use IF kind of logic across many cells in a parallel fashion.
If I were to say how many buildings do we have, I could simply copy this left word using the fill handle, and there is six different buildings in this particular example. Social Security number of course it better be unique. If we were to copy this left word here, we see that we have got 741 names. So we should have at least that many Social Security numbers, and that matches up from numbers and so on. How many different kinds of status do we have here? I am simply dragging this to the right. There are four, and different hire dates and so on.
So how many different benefits type packages do we have here? That one doesn't work because we have got empty cells. So this formula as we see it here is of limited use. It takes about 10 minutes to explain ultimately how this does work, but believe me it works and it's great. You can adopt it to your own needs. It is a way to count unique entries within a range. Again, I keep hoping from Microsoft to introduce a new function called unique, and it make this a lot simpler.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90696 Viewers
80 Video lessons · 137975 Viewers
59 Video lessons · 56753 Viewers
52 Video lessons · 70374 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.