Array SUM Formula for SUM with multiple criteria and a date range (Using Excel 2003)

I have a list of timesheet data for various employees where I am trying to sum the number of hours worked for a particular Sunday week commencing date by employee. I had this working when it was looking at one month in isolation but I have been asked to change this so that when the week covers two months that the “this weeks” summary of hours worked still takes into account the full 7 days not just the part week that applies to the current month

Eg My data is structure like this

Employee Code = an alpha code that is unique per employee
Charge Flag = can be Y or N
Hours Worked = number of hours from the timesheet
Doc_MM = the month number that the timesheet relates to ie 4 = April
Doc_DD = the day number that the timesheet relates to

So for week commencing Sunday the 27th April 2008 I need to sum the number of hours worked by employee for timesheets with a chargeable flag = Y for the following doc_MM and doc_dd combinations

Doc_DD Doc_MM
27 4
28 4
29 4
30 4
1 5
2 5
3 5

Remembering that the raw data for this example would contain all the timesheets for the month of April and May.

This spreadsheet is updated by a user each week and the focus W/C date will change each week

Any help would be greatly appreciate

Thanks

Karen

Hi,

why don't you make an extra column Doc_WK where you have the weeknumber of the date (using WEEKNUM-function) and summarize the values by that column?

- Asser

Below is an array I use to sum data from diffferent tabs using variance criteria. I hope this helps.


=SUM(IF(('(7.2b) Raw Lawson Data'!$A$6:$A$6000=9291)*('(7.2b) Raw Lawson Data'!$E$6:$E$6000=$A14),'(7.2b) Raw Lawson Data'!$D$6:$D$6000,0))+SUM(IF(('(7.2b) Raw Lawson Data'!$A$6:$A$6000=9211)*('(7.2b) Raw Lawson Data'!$E$6:$E$6000=$A14),'(7.2b) Raw Lawson Data'!$D$6:$D$6000,0))

Hi Asser

The weeknum function will probably cause me issue in the Dec/Jan cross over period.

However thank-you for your post as your suggestion gave me the idea to use vlookup to just add some text next to the dates for that week and use that as one of the criteria

Sometimes you just need a new set of eye's to look at something

Cheers

Karen

Hi Karen,

I'm glad I could give you a kick in a new direction If you need any help in summarizing the data, just post in this thread and I'll try to help you as I can.

- Asser