I know there's a pretty compley formula out there that counts the occurence of say Fridays in 2009  does anyone have this? I had it before in a file but ranged valued the results showing the count of each day of the week for each month in a given year.
Formula To Count Weekday Occurrence Based On Month And Year
We will be implementing some important changes during 25th and 26th May 2024 which may result in an outage period of the website. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.



Re: Formula To Count Weekday Occurrence Based On Month And Year
Try this, changing dates and days at will (day 6 is Friday):
= SUMPRODUCT( (WEEKDAY( ROW( INDIRECT( DATE(2009,1,1) & ":" & DATE(2009,12,31) ) ) ) = 6 ) )

Re: Formula To Count Weekday Occurrence Based On Month And Year
How could I adjust formula to be just month specific? For example to count the #'s of Friday for May, then June, then July, etc?

Re: Formula To Count Weekday Occurrence Based On Month And Year
Or, if you're willing to add a small function:
CodeFunction LA(n1 As Long, n2 As Long) ' returns a literal aray from n1 to n2 Dim v As Variant, i As Long if n2 < n1 Then Exit Function ReDim v(n1 To n2) For i = n1 To n2 v(i) = i Next LA = v End Function
... then you can shorten the formula to,
=SUMPRODUCT( (WEEKDAY( LA( DATE(2009,1,1), DATE(2009,12,31))) = 6) )

Re: Formula To Count Weekday Occurrence Based On Month And Year
Do you see the DATE functions in the formula?


Re: Formula To Count Weekday Occurrence Based On Month And Year
Yes  how would I just the formula to just reference the specific month and year?

Re: Formula To Count Weekday Occurrence Based On Month And Year
The first argument to DATE is the year, the second is the month, and the third is the day.
If you had dates listed in column A2:A13 (1/1/2009, 2/1/2009, ..., 12/1/2009), and weekdays listed in B1:H1 (1 ... 7), then enter this formula in A2 and copy down and left:
=SUMPRODUCT( (WEEKDAY(LA(DATE(YEAR($A2), MONTH($A2), 1), DATE(YEAR($A2), MONTH($A2) + 1, 0))) = B$1) )
... and you'd have a table of all the days of the week in each month.

Re: Formula To Count Weekday Occurrence Based On Month And Year
Thanks  This is perfect!

Re: Formula To Count Weekday Occurrence Based On Month And Year
Jumped the gun  getting #NAME? as a return value. Can you post sample spdsht w/ formula w/ example. I can't believe I can't get this to work properly!

Re: Formula To Count Weekday Occurrence Based On Month And Year
Also  I'm opting not to use the VBA code.


Re: Formula To Count Weekday Occurrence Based On Month And Year
Please find attached.

Re: Formula To Count Weekday Occurrence Based On Month And Year
Another way to achieve this would be to use this formula in B2 copied across and down
=5(DAY(DATE($A$17,$A2,36))>WEEKDAY(DATE($A$17,$A2,1B$1)))

Re: Formula To Count Weekday Occurrence Based On Month And Year
Hmm. In the workbook that I posted? It references A17, which is not in the table, and gives #NUM! when I copy horizontally. Can you clarify?

Re: Formula To Count Weekday Occurrence Based On Month And Year
My apologies, shg, I posted a formula I was using for testing. Yes, it was based on your example and should have referenced A1 containing the year, rather than A17, i.e.
=5(DAY( DATE($A$1,$A2,36))>WEEKDAY(DATE($A$1,$A2,1B$1)))
btw, congratulations on your 1000 posts

Re: Formula To Count Weekday Occurrence Based On Month And Year
Yes, shg, well done on 1,000 posts!


Re: Formula To Count Weekday Occurrence Based On Month And Year
Shg congrats on 1,000 posts.

Re: Formula To Count Weekday Occurrence Based On Month And Year
Thank you all. I've learned more Excel here in the past seven months than in the seven years prior.

Re: Formula To Count Weekday Occurrence Based On Month And Year
B2bFall2005, I think you got the "#Name" error because that function called the LA() function defined in the VBA code. If you did not have the VBA code in your sheet, the LA() function won't work.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!