# Count number of unique values which are a given day of the week in a list of dates.

1. ## Count number of unique values which are a given day of the week in a list of dates.

Given a list of dates, where some of the dates are repeated, how could I determine how many distinct Wednesdays (or any other day) are included in those dates?

Thanks.

2. ## Re: Count number of unique values which are a given day of the week in a list of dates.

There are some possible methods, but it's almost impossible to know without seeing how the data is organized. Can you upload a sanitized sample file?

3. ## Re: Count number of unique values which are a given day of the week in a list of dates.

Try this...

=SUMPRODUCT((WEEKDAY(A2:A60)=4)/COUNTIF(A2:A60,A2:A60&""))

Where your data is in Range A2:A60. Change highlighted value for other weekdays

4. ## Re: Count number of unique values which are a given day of the week in a list of dates.

Hi.

Assuming that your dates are Excel-recognized dates (and not e.g. text) and that they lie in A1:A100, and also that you put your day of choice, using its full English naming, in B1, this array formula**:

=SUM(IF(FREQUENCY(IF(TEXT(A1:A100,"dddd")=B1,A1:A100),A1:A100),1))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Many thanks!

6. ## Re: Count number of unique values which are a given day of the week in a list of dates.

Hello ACE_XL,

I am having difficulty in understanding a very similar formula. Kindly use the link below:
http://www.excelforum.com/excel-form...om-a-list.html

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