Counting number of unique dates per month in a list of duplicate dates

**Rackle83** · 04-22-2013, 09:57 AM

I have a list of dates when an exercise is taking place, I want to count the number of unique dates in the month that have been selected, for each month...eg:

Date of exercise
10/06/2013
27/06/2013
24/06/2013
24/06/2013
27/06/2013
27/06/2013
11/06/2013
27/06/2013
10/06/2013
27/06/2013
20/07/2013
17/07/2013
14/07/2013
14/07/2013
17/07/2013
17/07/2013
11/07/2013
17/07/2013
20/07/2013

Data I want to gather would be number of exercises booked for June, and number of exercises booked for July assuming 1 exercise per date.
Can anyone offer any advise of the best formula to use to achieve this?

I have tried several formulas (sumproduct, Frequency, countif) but can't get the right combination to achieve my overall goal.

**Melvinrobb** · 04-22-2013, 10:02 AM

Assuming the Date of Exercise starts in A1, I would insert a helper column in B1, using this formula in B2 and dragging down:
=if(countif($A$1:A1,A2)=0,1,0)

You can then Sum column B to get the total # of unique values.

**daddylonglegs** · 04-22-2013, 10:03 AM

This array formula will give you the number of different June dates

=COUNT(1/FREQUENCY(IF(MONTH(A2:A20)=6,A2:A20),A2:A20))

confirmed with CTRL+SHIFT+ENTER

Note: it's not year specific so if you only want June 2013 change to this version

=COUNT(1/FREQUENCY(IF(TEXT(A2:A20,"mmm-yy")="jun-13",A2:A20),A2:A20))

**Tony Valko** · 04-22-2013, 10:06 AM

Try something like this...

For the unique dates in the month of June (month #6)...

Array entered**:

=SUM(IF(FREQUENCY(IF(MONTH(A2:A20)=6,A2:A20),A2:A20),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**Rackle83** · 04-22-2013, 10:12 AM

Thanks! This worked!
Wish I had thought to come on here 3 hours ago!

**Tony Valko** · 04-22-2013, 10:15 AM

You're welcome. Thanks for the feedback!

Counting number of unique dates per month in a list of duplicate dates

LinkBack

Thread Tools

Rate This Thread

Display

Counting number of unique dates per month in a list of duplicate dates

Re: Counting number of unique dates per month in a list of duplicate dates

Re: Counting number of unique dates per month in a list of duplicate dates

Re: Counting number of unique dates per month in a list of duplicate dates

Re: Counting number of unique dates per month in a list of duplicate dates

Re: Counting number of unique dates per month in a list of duplicate dates

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions