Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,987 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
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.
- Multiple key sorting
- Single and multiple column numeric filters
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX