Every now and then I find this one tip that’s worth alot … here’s how to count unique values in one column in Excel
The method to count the uniques is quite simple and was found after a few minutes spent on Google. Here’s how I finally did it after checking out a few methods (most of them didn’t work):
- Select the cells you want to count the uniques for, preferably in one and the same column.
- Right click on that selection and choose “Name a Range” from the menu
- Give that range a name (
- Go to any given cell where you want the unique count to appear in
- Enter this as formula:
emailsis the name we just gave to the range
- Now the tricky part: don’t just press
enter, but press
What the other sites don’t tell you…
I have noticed that the results may be incorrect though when the range has not been sorted. Only when sorting your range ASCENDING the correct result will be given. To make it complete: Sorting your values DESCENDING will give you an
#N/A as count.
Happy counting! 😛
Be sure to check the comments below to see how this works in Excel 2007