My DOTD : Count unique column values in Excel

Office 2007 IconEvery now and then I find this one tip that’s worth alot … here’s how to count unique values in one column in Excel


Counting (C)rows

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 (emails for example
  • Go to any given cell where you want the unique count to appear in
  • Enter this as formula: =SUM(IF(FREQUENCY(IF(LEN(emails)>0;MATCH(emails;emails;0);"");IF(LEN(emails)>0;MATCH(emails;emails);""))>0;1)) where emails is the name we just gave to the range
  • Now the tricky part: don’t just press enter, but press SHIFT+CTRL+ENTER

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

Join the Conversation

7 Comments

  1. Another option is to use pivot tables
    Drag your values to the left side (or top if you want) and in the data field
    Than set your mode of the pivot table to count.
    That way you get a 1 in the data area of all uniques (= the number of times it appears)
    No magic, just the almighty pivot table

  2. Don, great tip!

    Worked indeed for string values, but with numbers it – by default – gives back the sum.

    Tried setting it to a different type of value (RMB > “Summarize Data by…”), but cannot choose “uniques” or something alike anywhere. Something I’m looking over?

  3. You must be overlooking something I think.
    My excel does the job just fine on both text and numbers.
    The mode of your pivot needs to be ‘count of’ instead of the default ‘sum’ though.

  4. In Excel007 (yup) – one needs to change the colons (;) to commas (,) and then everything else the author said works fine.

  5. thanks for this, and also to florian for the excel 2007 fix. my problem is that i have multiple-word string values, and this formula only counts the first word. i figured out because filtering gives me 7 filter categories but this formula only shows 3 (there are some that share the same first word so the formula fails to recognize uniqueness post-first-word). No that I know the count is relatively small I can do what I need by hand, but just wanted to share this caveat with everyone…

  6. Somehow nothing written here seemed to work for me in Excel 2007. Further googling gave me this formula: =SUMPRODUCT((A1:A10″”)/COUNTIF(A1:A10;A1:A10&””))
    that surprisingly easy and even without the need to type CTRL SHIFT ENTER, shows the unique results for A1:A10.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.