DPCroc

Hi, I am running a spreadsheet that attracts data from other sheets in the workbook on a weekly basis. At the end of the month the values need to be summated.

That's easy. The problem is related to the number of Mondays in any one month in any one year. That's easy to find out which months have only 4 and which have 5.

The problem I have is to "automatically", if you like, recognise these variations as they occur each year so that the correct range of cells is selected to take into account the movement of these 4's and 5's from year to year.

I have tried looking at =rows() but can't seem to crack the formula needed for recognising the annual changes. May not even be this function that I should be looking at.

Hoping this makes sense to someone and that there is a simple answer. Beginning to wonder if I am "over-thinking" the problem.

Thanks in advance...


DPCroc

macropod

DPCroc,

The answer depends on how your worksheet is laid out - are the data to be summed in columns or rows (where are the date & value ranges), and is there more than one month's data on the worksheet.

DPCroc

Thanks for that respose...

Sorry about the delay. Had to put this project on the back-burner for a day or two.

The data is in Columns and there is 12 months to be gathered. The following year will not have the same months with 5 Mondays in it.

Hope that helps you see the plan!!!

Regards,

DPCroc

macropod

Hi DPCroc,

Assuming, for example, your dates are in A1:A500 and the values are in B1:B500, the following formula will tally just the values for Mondays in May, 2009:
=SUMPRODUCT((WEEKDAY(A1:A500)=2)*(MONTH(A1:A500)=5)*(YEAR(A1:A500)=2009),B1:B500)
If you simply want all Mondays in May, regardless of the year, use:
=SUMPRODUCT((WEEKDAY(A1:A500)=2)*(MONTH(A1:A500)=5),B1:B500)

DPCroc

Hi Macropod,

Thanks a million..I can see how that would work. Now to make it work!

Regards,

DPCroc