Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you need to count how many unique entries there are in a column or a row, a special array formula will give you a quick answer. Now this is an unusual formula that meets the need for the fact that there really is not a function in Excel called Unique. As we look at this data on this worksheet, the question might come up, how many different entries do we actually have in Column C? How many different buildings do we have? Those are things you would want to know. Perhaps you know some of them. How many statuses we have? We know there are four, for example here.
But way to do this and you don't necessarily need to do this above your data, but I think we might want to put it here at least temporarily. By inserting a new row above Row 1 here and here is the formula. Now it's an unusual formula and it is an array formula and in effect what we like to be doing here is counting or comparing every single name here with every other name to see if there are any duplicates. What we really are trying to do though is count the number of different entries. We recognize that possibly we might have two people with the same name.
But why not do this for every column? So here is the function. Let me zoom in on this too, because it is unusual. And in this column, let me do a quick check at the bottom here. There are 763 rows right now, since we inserted a new one. So we want to keep that number in mind. =sum(1/countif( and I don't want to highlight all these cells but I do want to start dragging here at least down to here and I'll just complete the typing by putting in the 63.
So we want to take everyone of these cells here. I am going to copy so I don't want to have to retype it. And compare with everyone of those cells to count how many times it appears there. )). Again, it's a hard function to explain and it would take at least five minutes. There is more information in another course on Excel Advanced Formulas and Functions, both through 2010 and 2007 you might want to take a look at. But I am going to be pressing Ctrl+Shift +Enter here to get a count of how many unique entries there are from cells A3 to cells A763 and there is the answer.
741, so all the names are unique. Let's do a quick test here. I am going to duplicate Troy Sanders name just by copying it downward here. Now of course we now have 740. Let me undo that by pressing Ctrl+Z. How many different buildings do we have? We'll just copy this to the right here. Six different buildings. How many different departments do we have? 23 different departments and so on. Possibly you'd want to copy this all the way across. Or once you had done this, if this is vital information and you want to keep it handy, maybeyou put it to the right side of your list or possibly below it, wherever you wish.
One-by-one each of these formulas has a similar look to it, and they are very unusual, but it gets the job done. It allows us to quickly make a count of how many unique entries there are in a range.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91364 Viewers
80 Video lessons · 138252 Viewers
59 Video lessons · 57084 Viewers
52 Video lessons · 70730 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.