In this tutorial we are going to use a simple but creative way to determine the number of unique items in a list in Excel. We’ll use the SUM and COUNTIF functions in an array formula to accomplish this. We’ll also do it manually using Remove Duplicates as a comparison.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a list of 17 items that has several duplicates.
If I highlight the list and use the “Remove Duplicates” on the Data tab of the Ribbon, you can see that there are 7 unique items on the list:
However, if I want to use a formula to accomplish this, here is what I would enter:
Note the curly brackets surrounding the formula. This is an array formula, requiring it to be entered using Ctrl + Shift + Enter rather than just Enter. The reason is that the COUNTIF portion of the formula is comparing two arrays, so that makes it an array formula. And, as you can see, we got the same answer – 7:
So, how does this work? The COUNTIF function compares the array to itself to determine how many of each unique item there are included in the list. The totals are:
Then Excel takes each instance and divides that into one to get the fraction that each instance is of the total. I’ll use VLOOKUP to show result here:
When you total up the fractional portion of each instance, you get 7, the total number of unique items in the list! Check out the video to see exactly how the process works.
And that’s how easy it is to do this in Excel!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!