How can Excel count the number of Friday's in a month?

[Gus Arnstrom]

A week is 7 days therefore if there were 4 weeks in a month there would only
be 28 days. This is not true with a real calendar (Except for Feburary). 11
out of 12 months have 30 or 31 days. Lets say the month starts on a Friday
and has 31 days. This means that in that month there are 5 Friday's, 5
Saturday's, and 5 Sunday's, but only 4 of each other day of the week. I need
to put a formula in Excel that will count the number of a specific day of the
week and I don't know how to do it. I thought of a countif statement but I
can not figure out how to connect it a calander. The WORKDAY formula is close
to what I want but not exactly. In Excel, WORKDAY would return a 5 for
Friday, but I still need a way to test if there are 4 or 5. I have 2 cells;
one say "Number of weeks", and the other has the number 4. This number is
used in calculation of other cells. I do not want to have to manually change
this cell from 4 to 5 every time a month has 5 of a certain weekday. (In my
case Wednesday). This happens 4 times a year approximantly every 3 months.
Any help would be wonderful.

 

[Frank Kabel]

Hi
if A1 stores the beginning of your month and A2 the end of your month
try:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1 & ":" & A2)))=6))

 

[Domenic]

=SUMPRODUCT(--(ISNUMBER(MATCH(TEXT(ROW(INDIRECT(B1&":"&C1)),"dddd"),A1,0)
)))

....where A1 contains the day of the week of interest (eg. Monday,
Tuesday, etc.), B1 contains the date for the first day of the month (eg.
10/1/2004), and C1 contains the date for the last day of the month (eg.
10/31/2004)

Hope this helps!