# Excel - Count distinct occurences and copy unique values

1. ## Excel - Count distinct occurences and copy unique values

Hello,

I am seriously new to MS Excel. What I'm trying to accomplish is:
``Please Login or Register  to view this content.``
So I have the dates column but want to generate the distinct dates (unique dates) and # of occurrences that date is listed.

Currently using MS Excel 2003 and/or Google Sheets and/or LibreOffice.

The end product is to generate a line graph.

Thanks

btw: this is a xpost from the submission I did over at Stackoverflow.com
http://stackoverflow.com/questions/3...-unique-values

2. ## Re: Excel - Count distinct occurences and copy unique values

Try this...
 A B C D E F 1 Dates Distinct dates # of occurrences 2 13-Feb-14 13-Feb-14 8 3 13-Feb-14 14-Feb-14 5 4 13-Feb-14 5 13-Feb-14 6 13-Feb-14 7 13-Feb-14 8 13-Feb-14 9 13-Feb-14 10 14-Feb-14 11 14-Feb-14 12 14-Feb-14 13 14-Feb-14 14 14-Feb-14

C2=IFERROR(INDEX(\$A\$2:\$A\$14,MATCH(0,INDEX(COUNTIF(\$C\$1:C1,\$A\$2:\$A\$14),),0)),"")
copied down

E2=COUNTIF(\$A\$2:\$A\$14,C2)
copied down

3. ## Re: Excel - Count distinct occurences and copy unique values

Originally Posted by FDibbins
Try this...
 A B C D E F 1 Dates Distinct dates # of occurrences 2 13-Feb-14 13-Feb-14 8 3 13-Feb-14 14-Feb-14 5 4 13-Feb-14 5 13-Feb-14 6 13-Feb-14 7 13-Feb-14 8 13-Feb-14 9 13-Feb-14 10 14-Feb-14 11 14-Feb-14 12 14-Feb-14 13 14-Feb-14 14 14-Feb-14

C2=IFERROR(INDEX(\$A\$2:\$A\$14,MATCH(0,INDEX(COUNTIF(\$C\$1:C1,\$A\$2:\$A\$14),),0)),"")
copied down

E2=COUNTIF(\$A\$2:\$A\$14,C2)
copied down
Oh my goodness... Okay let me get back to you on this one. Need to eat lunch first and have to go to a meeting afterwards

4. ## Re: Excel - Count distinct occurences and copy unique values

Originally Posted by random0munky
Oh my goodness... Okay let me get back to you on this one. Need to eat lunch first and have to go to a meeting afterwards
Originally Posted by random0munky
Oh my goodness... Okay let me get back to you on this one. Need to eat lunch first and have to go to a meeting afterwards
Unfortunately, I wasn't able to get this to work, but it was probably because of user error than the function not working correctly. I actually went the route of using a Pivot Table, adding the dates to the Row and adding the dates again to the Values and using CountA which gave the # of occurrences. Thanks again for the help =)

5. ## Re: Excel - Count distinct occurences and copy unique values

If you're using Excel 2003 then the IFERROR function is not supported in that version.

Do you want another formula option or are you satisfied using the pivot table?

6. ## Re: Excel - Count distinct occurences and copy unique values

Originally Posted by Tony Valko
If you're using Excel 2003 then the IFERROR function is not supported in that version.

Do you want another formula option or are you satisfied using the pivot table?
Thanks Tony for reply and offer. I'm fully content with using pivot tables. Thanks

7. ## Re: Excel - Count distinct occurences and copy unique values

Good deal. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

8. ## Re: Excel - Count distinct occurences and copy unique values

Thanks for the feedback

Tony, good catch on the 2003/IFERROR, I didnt notice that

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1