Find and count days matching particular weekday, particular month and particular year from within a list of random dates

[Intermediate]

Hi,

I have a random list of dates from multiple years in column A:A. I would like to count how many of those random dates are eg. Saturdays in November 2013 or Mondays in October 2012. Any ideas?

Thanks in advance for your time and help.

 

[Intermediate]

As is often the case - having written it out on a forum I've solved it myself. It's not an elegant solution but it does the job. If anyone has a cleaner way, with creating additional columns, please post. My clunky way is as follows:
List of random dates are eg. A1:A1000
Name Column B "DAY", and in B1, =WEEKDAY(A1,2) [2 counts the first day of the week as a Monday instead of Sunday]
Name Column C "MONTH", and in C1, =MONTH(A1)
Name Column D "YEAR", and in D1, =YEAR(A1)
Select B1:D1 and drag down to eg. D1000 to copy formulae alongside dates

In E1, =COUNTIFS(B:B,6,C:C,11,D:D,2013) [this will count rows where Day=Saturday, Month=November, Year=2013]

 

[Andrew Poulsom]

Example:

=SUMPRODUCT(--(TEXT(A1:A1000,"ddd")="Sat"),--(TEXT(A1:A1000,"mmm yyyy")="Nov 2013"))

 

[markmzz]

Maybe this (a small modification in Andrew's formula):

Layout

Dates		Weekday	Month	Year	Result
21/08/2013		Saturday	November	2013	3
19/07/2013		Monday	October	2012	11
16/08/2013
07/08/2012
12/06/2012
04/12/2012	**	*********	**********	*****	******

<tbody>
</tbody>

Formula

In F2

=SUMPRODUCT(--(TEXT($A$2:$A$1000,"ddddmmmmyyyy")=$C2&$D2&$G2))

Markmzz

 

[markmzz]

Sorry, no need modification.

 

[Intermediate]

Thanks guys - far more elegant! I appreciate it.