headermask image

Business Tools Blog

Count unique data in a range, exclude duplicates in Microsoft Excel

Photobucket

Question from Rachelle Haid

I have been researching this for a bit and can’t figure out…

Do you know how to check for duplicates in a list and only have it return a 1 on the first instance, so I only count unique values….

For example if I have a list of ID’s (1001, 1002, 1003,1004,1001)

1001 appears twice but I don’t want to count it twice only once…

Any ideas???

Hi Rachelle - we missed you last week!

The easiest way to count unique values is using a pivot table.  The pivot table will automatically group duplicate IDs.

If you want to use a formula, combine SUM and FREQUENCY:

  • I re-created your list of ID’s in column A, rows 1 thru 5
  • Type the formula =SUM(N(FREQUENCY($A$1:$A$5,$A$1:$A$5)>0))
  • This formula counts all ID’s one time, excluding the duplicates

Photobucket

If you liked my post, feel free to subscribe to my rss feeds

Money Making Options

Post meta

This entry was written by Sandi Mays and posted on September 22, 2009 at 12:28 am and filed under Excel, Rachelle Haid. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment(Latest is displayed first) or leave a trackback: Trackback URL.

Share with others

Post Count unique data in a range, exclude duplicates in Microsoft Excel to digg. Post Count unique data in a range, exclude duplicates in Microsoft Excel to Reddit add to stumbleupon Google Bookmark myweb bookmark on del.cio.us newsvine Tailrank magnolia Furl co.mments shadows simpy blinklist

2 Comments so far (Add 1 more)

  1. Mike,

    I use custom filters all the time, and had never used the Unique Records Only filter. Thanks for the tip!

    1. Sandi Mays on September 16th, 2008 at 2:03 pm

  2. I use the advanced filter function, checking the ‘Unique Records Only’ option. It then gives you the filtered total and overall total on the bottom status bar. I’ll have to try the pivot table method.

    2. Mike C on September 16th, 2008 at 12:58 pm

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*